“Microsoft Excel is not a spreadsheet application anymore, it’s a medium to store, present, and analyze data” – Puneet Gogia, Co-Founder ExcelChamps
There’s no question to ask that it’s important for every professional to learn Microsoft Excel, but the point which I want to make here is:
If you know some of the things which normal people don’t know that they can do in Microsoft Excel, you can outrank them.
So today in this post, I’d like to share with you “9 things in Microsoft Excel You Must Learn to Stay Ahead from Your Colleagues”. And the best part is these options are easy to understand and can save you a few hours every month.
…so without any further ado, let’s get started.
1. Conditional Formatting
As the name suggests, with the conditional formatting you can format a cell when a condition is met. In simple words, it’s a smart way of formatting cells.
Suppose you want to apply red color to all those cells where the value is below 10, you can do this with conditional formatting instead of doing it manually one by one.
To access conditional formatting all you need to do is go to the Home Tab ➜ Styles ➜ Conditional Formatting.
From here you can apply different kinds of formatting on a cell or a range of cells. Some of the useful options are:
- Duplicate Values: With this, you can highlight all those cells where you have duplicate values. To apply this, just select the range of the cells and click on this option.
- Top 10 Items: This option can help you to highlight the top 10 values from a long list of values.
- Color Scale: It gives a shaded color to a cell by comparing the value it has with all the values in the selected range.
Apart from the options, you have there, you can create a “New Rule” using your formula and method.
2. Named Range
In Excel, every cell has its address which is made up of the row number and column alphabet. But you can create a custom name for a cell or a range of cells.
The benefit of creating a named range is, it’s easy to identify the value you have in it as you can give a relevant name to it.
Below are the steps you need to follow to create a named range in Excel:
- First of all, select the cell or range of cells that you want to convert into a named range (here in this example, I have a cell where I have a discount percentage).
- After that, go to the “Formula” tab and click on the “Name Manager” and then click on the “New”.
- Now here you need to specify:
- Name which you want to give to the range (I’m using “Sales_Discount” here)
- Scope (availability of the named range).
- Comment, if you want to add.
- And then the address of the range which is already there as you have selected the cell.
- In the end, click OK.
3. Format Painter
The simple idea behind the format painter is to apply cell formatting from a range of cells to another range of cells…
…in two clicks.
Let’s see it in action.
In the below example, I have data in column A and then the same type of data in column C and I want to apply the formatting which I have on column A to column C.
So here are the steps:
- First of all, select the column from where you want to copy the formatting and go to the “Home Tab” and then click on the “Format Painter” button.
- After that, select the range of cells where you want to apply the formatting.
The moment you select the cell, the format painter applies the formatting to it.
PRO TIP: If you want to apply cell formatting to multiple ranges, just double click on the format painter button and then you can be able to apply the formatting on multiple ranges.
4. Create a Drop Down
By creating a drop-down you pre-define the values you want to enter in a cell.
It also saves time as you don’t need to enter values, again and again, you just need to select it from the list.
Follow the below steps to create a drop-down list in Excel:
- First of all, go to the “Data Tab” and click on the “Data Validation” button in the Data Tools group.
- Now, in the dialog box, select the list from the “Allow” and in the source input bar enter the values you want to use in the dropdown.
- In the end, click OK.
Tip: Instead of directly entering values in the source input bar you can refer to a range where you have values.
5. Flash Fill
Flash fill is one of the smartest options in Excel which you can use to save time in your everyday work.
Let me share with you a simple example to make you understand it.
In the below example, I have a list of email IDs and I want to extract names from these IDs.
So all I need to do is, for the first email ID enter the name in the cell B2.
As the name in the email ID is “Puneet” so I entered Puneet.
Now come to the cell B3 and press the shortcut key Control + E.
The moment you press it, Flash Fill will extract names from all the other IDs.
It works in a flash that’s why its name is Flash Fill. But now, let me tell you how it works so that you can use it in your way.
When I enter the name in the cell B2 that name was before the “@”, so Flash Fill has used the same pattern and extract all the text value which are before “@” from the rest of the cells.
6. Apply Table
Whenever you have data in a worksheet make sure to apply the table to that data.
Below are some of the benefits which you can have:
- Multiple Formatting Options
- Easy to Filter and Sort
- Add Total (Count, Sum, Average, and many more) Row
- Name the Entire table
- Create a Pivot table easily
- Many more…
You can use any of the below methods to apply it.
- The first method is to use the shortcut key Control + T to apply the table to the data.
- And, the second method is to go to the “Data Tab” and click on the “Table” button.
When you apply the table by using both of these methods, Excel will ask you if your data has its heading or not and if you don’t have a heading, it will add a heading line automatically.
7. Slicer and Timeline
In Excel, the normal filter option is quite popular to filter data, but with Excel 2013 version Microsoft had introduced two new ways to filter data
Once you create a pivot table, you can insert a slicer or a Timeline to filter your pivot tables.
Just select the pivot table and go to the Analyze Tab to insert Slicer or a Timeline.
With a slicer, you can filter value using a specific column that you need to select while inserting it.
When you select a column to add in the slicer it shows all the value which are there as tabs and you can click on a tab to filter.
On the other hand, the Timeline filter is pretty specific. It works with dates and can help you smartly filter data.
You can customize it for filtering dates with Years, Quarters, Months, Days.
8. Paste Special
Paste Special can help you efficiently paste data.
When you copy something in Excel from a cell, paste special gives you the option to paste that data in a customizable way.
Let’s say you just want to paste the value, not the formatting that values have one the cells.
Or, you just want to paste formatting, not the values.
There are more than 15 options there in the Paste Special which you can use and once you copy something, you need to go to the Home Tab and click on the paste special drop-down to see all the available options.
Or, you can also use the Alt + H V S keyboard shortcut.
9. Install Add-in
In Excel, you can install add-ins from the App store which can help you to increase your efficiency.
If you are using Excel 2013 and above then you have the option to install an add-in.
In the Insert Tab, you have the option to “Get Add-Ins”. Once you click on it you’ll have a dialog box to search for the add-in or you can select a category to find an add-in.
There are a lot of add-ins which you can use and below are some of my favorites:
Just jump into the Excel’s App store and try finding Add-Ins which are relevant for you.
In the End
I want everyone out there who works on the data, even a little bit, to explore more about Excel.
And I hope all these tips which I have shared with you can help you to save you a ton of time in the coming weeks and spark curiosity in you to learn more.
Do you have any favorite Excel option which helps you to save time?
Please share with me in the comment section, I’d love to hear from you and make sure to share this Microsoft Excel blog post with your friends, I’m sure they’ll appreciate it.