We might earn a small income when you click on some of our links.

“Microsoft Excel is not a spreadsheet application anymore, it’s a medium to store, present, and analyze data” Puneet Gogia, Co-Founder ExcelChamps

From a front desk coordinator to the CEO of a company, it’s for everyone. From a tech startup to a manufacturing plant, it’s everywhere. In short, everyone uses it.

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.

Conditional Formating

To access conditional formatting all you need to do is go to the Home Tab ➜ Styles ➜ Conditional Formatting.

Pivot Table

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”.

Name Manager in Excel

  • 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.

Edit Name Excel

  • In the end, click OK.

Setting up Formulas in Excel

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.

Formating in Excel

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.

Data Validation in Excel

  • 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.

Data Validation in Excel

  • 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.

Flash Fill in Excel

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…

Pivot Table Excel

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.

Convert PivotTable to Table

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

  • Slicer
  • Timeline

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.

Filter Connections Excel

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.

Formulas in Excel

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.

Past Special in Excel

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.

Microsoft Office Add-ins

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.

Written By
Puneet Gogia is the Co-Founder of ExcelChamps. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

Related Post