# Top 100 Microsoft Excel Tips and Tricks which will Make You a PRO this Year + PDF Download

`(adsbygoogle = window.adsbygoogle || []).push({});`

Every Excel user do need a list of tips and tricks which he/she can use to learn to save time. Even if you learn a single tip a day you can learn 30 new things in a month.

It's a lot.

But the point is: you must have a list which you can refer to every day.

Well, you don’t need to worry about this anymore, I’m here to help. I have listed Top 100 Excel Tips and Tricks for you. And this is the most comprehensive list with all the basic and advanced tips.

Yes it is, and all the tips and tricks are listed under a specific category which makes easy to follow the entire list.

## List of Top 100 Excel Tips and Tricks You need to Learn this Year to Increase your Productivity in No Time

Without any further ado below is the list of tips and tricks that can make you an advanced Excel user this year.

In this entire list, I have marked some of my favorites tips  which I love to use and I’m sure you’ll also love them.

Get the PDF

If you are in a hurry, click the button below to get the PDF version of this post.

Exclusive Content for ExcelChamps Subscribers

Basic Tips

If you are just starting out with Excel then these tips can save you a lot of time and can help you to get better quickly in everyday tasks.

### 1. Serial Numbers

For me, the best way to do this is to apply the table to the data and then add 1 in the above serial number, just like below.

To do this, you simply need to add 1 to the first cell of the column and then create a formula to add 1 to the above cell’s value.

As you are using a table, whenever you create a new entry in the table, Excel will automatically drop down the formula, you’ll get the serial number.

### 2. Current Date and Time

Sub timeStamp()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
End Sub

Normally we all do it this way, hold the control key and the select cells one by one. But, I have found that there is a far better way for this.

All you have do is, select the first cell and then press SHIFT + F8, this gives you add or remove selection mode in which you can select cells by just selecting them.

### 4. Sort

If you deal with the data which needs to sort frequently then it’s better to add a button to the quick access toolbar (if it's not there already).

All you need to do is click on the down arrow on the quick access toolbar and then select “Sort Ascending” and “Sort Descending”.

It adds both of the buttons to the QAT for “Ascending” and “Descending”.

### 5. Drag and Drop

Just think this way you have a small data table and you want to move it from one section of your worksheet from another.

I’m sure you think about copy-paste but you can also use drag-drop for this.

And all you need to do is select the range of cells where you have data and then click on the border of the selection.

By holding it move to the place where you need to put it.

### 6. Status Bar

The status bar is always there but we hardly use it to the full. If you right click on it you can see there hell lot of things which you can check using status bar.

By holding it move to the place where you need to put it.

### 7. Clipboard

There is a problem with normal copy-paste that you can only use a single value at a time.

But here is the kicker:

When you copy a value it goes to the clipboard and if you open the clipboard you can paste all the values which you have copied.

To open a clipboard click on the go to Home Tab ➜ Editing and then click on the down arrow.

It will open the clipboard on the left side of the window and you can paste values from there.

### 8. Bullet Points

• Press Ctrl + 1 and you will get the "Format Cell" dialog box.
• Under the number tab, select custom.
• In type input bar, enter the following formatting.

● General;● General;● General;● General

• Click OK.

Now, whenever you insert a value in the cell Excel will add a bullet before that.

### 9. Copy of Worksheet

To create a copy of a worksheet in the same workbook drag and drop in the best way. Now, whenever you insert a value in the cell Excel will add a bullet before that.

### 10. Undo-Redo

Just like sort buttons you can also add undo and redo buttons to the QAT.

The best part about that buttons is you can use them to undo to a particular activity without pressing the shortcut key again and again.

Formatting Tips

Formatting is one of the boring things when it comes to data and reporting and here we have some of the time saver formatting tips for you which you can use in your daily work.

### 11. Auto Format

The auto format is a combination of six different formatting and you have the option to disable any of them while applying it.

### 12. Format Painter

Let’s say you have specific formatting (Font, Cell Color, Bold, Border, etc.) in the range A1: A5 you can copy that formatting to range C1: C5 with a click.

• First of all, select the range A1: A5.
• After that, go to the Home Tab ➜ Clipboard and then click on “Format Painter”.
• Now select the cell C1 and it will automatically apply the formatting on C1: C5.

### 13. Cell Message

In this case, you can add a cell message for that particular cell. When the user will select that cell it will show the message you have specified. Here are the steps to do this:

• First of all, select the cell for which you want to add a message.
• After that, go to the Data Tab ➜ Data Tools ➜ Data Validation ➜ Data Validation.
• In the data validation window, go to the Input Message tab.
• Enter title, message and make sure to tick mark “Show input message when the cell is selected”.
• In the end, click OK.

Once the message is showed you can drag and drop it to change its position.

### 14. Strikethrough

But I have figured out that there are 5 ways to do it and easiest from all of them is a keyboard shortcut.

Control + 5

…and if you are using MAC then here the shortcut.

⌘ + ⇧ + X

Quick Note: You can use the same shortcuts keys if you need to do this for partial text in a cell.

### 15. Barcode ?

To create a bar-code in Excel all you need to do it install this bar-code font from ID-AUTOMATIC.

Once you install this font, you will have to type the number in a cell for which you want to create a bar-code and then apply the font style.

### 16. Month Name

• First of all, select the cell with a date and open formatting options (use Ctrl + 1).
• Select the “Custom” option and add “MMM” or “MMMMMM” for the month or “YYYY” for the year format.
• In the end, click OK.

Custom formatting just changes the formatting of the cell from date to year/month but the value remains the same.

### 17. Highlight Blank Cells

When you work with large data sheets it’s hard to identify the blank cells from it. So the best way is to highlight them by applying a cell color.

Here are the steps to do this.

• First of all, select all the data from the worksheet using the shortcut key Ctrl + A.
• After that, go to Home Tab ➜ Editing ➜ Find & Select ➜ Go To Special.
• From Go To Special dialog box, select Blank and click OK.
• At this point, you have all the blank cell selected and now apply a cell color using font settings.

That’s it…

### 18. Font Color with Custom Formatting ?

In Excel, we can apply custom formatting and in custom formatting, there is an option to use font colors (limited but useful).

[Green]#,###;[Red]-#,###;0;

To apply this all you need to do is:

• First of all, select the cells where you want to apply this format.
• After that open format option using keyboard shortcut Ctrl + 1 and go to “Custom” category and the custom format in the input dialog box.
• In the end, click OK.

### 19. Theme

We all have some favorite fonts and colors which we use in Excel.

Let’s say you received a file from your colleague and now you want to change the font and colors for the worksheet from that file.

The point is, you need to do this one by one for each worksheet which takes time.

But, if you create a custom theme with your favorite colors and font then you can change the style of the worksheet with a single click.

For this, all you have to do is apply your favorite designs to the tables, colors to the shapes and charts, font style and then save it as a custom theme.

Here’s how to do it.

• It opens a “Save As” dialog box, name your theme and save it.
• And now, every time you need just one click to change any worksheet style to your custom style.

### 20. Clear Formatting

This is a simple keyboard shortcut which you can use to clear formatting from a cell or range of cells.

Alt + H + E + F

Or, otherwise, you can also use clear formatting option from the Home Tab.

Home Tab ➜ Editing ➜ Clear Clear ➜ Formats.

Formula Tips

Formulas and functions are the core power of Microsoft Excel. In short, they can make you great and here we have some formula tips which can make you smart.

### 29. INDEX MATCH

As we all know there is no way to look up to left for a value using VLOOKUP.

### 30. SUMPRODUCT IF ?

Charting Tips

To make you better at charting, here some of my favorite tips and I’m pretty sure that these charting tips will help you to create charts in a smart way.

### 39. Default Chart

We can use a shortcut key to insert a chart but the problem is, it will only insert the default chart and in Excel, our default chart type is “Column Chart”.

So if your favorite chart is a line chart, then the shortcut is useless for you.

But let’s conquer this problem.

All you have to do is change your default chart and here are the steps for this:

• Go to Insert Tab ➜ Charts.
• Click on the arrow at the bottom right corner.
• Then in your insert chart window, go to “All Charts” and then select the chart category.
• Right, click on the chart style which you want to make your default Select “Set As Default Chart”.
• Click OK.

### 40. Hidden Cells

Printing Tips

In my clarity, one of the most time-consuming thing in Excel is printing reports.

Here is the list of some awesome printing tips which you can use will printing your reports (These tips were originally published on ExcelDemy).

### 46. Center on Page

This option is quite useful when you need to print some specific formats on a page.

It makes it rightly aligned on a page.

### 47. Print Area

Let’s say you have some specific data in a range and you want to print only that range.

Now, the simple way is to select that range and use the option “print selection”.

But, what if you have to print that range frequently.

Well, in this case, you can specify the printing area and can print it without selecting the range every time.

It will show all the margins applied and you can change them by just drag and drop.

### 48. Custom Margin

You know what I mean.

It will show all the margins applied and you can change them by just drag and drop.

### 49. Error Values

The thing is, you can replace all the error values while printing with another specific value.

• Go to File Tab ➜ Print ➜ Print Setup ➜ Sheet.
• Select replacement value from “Cell error as” drop down.
• You have three options to use as a replacement.
1. Blank
2. Double minus sign.
3. “#N/A” error for all the errors.
• After selecting the replacement value, click OK.

I believe using “Double minus sign” is the best way to present errors in a report while printing it on a page.

### 50. Custom Start Page Number

If you want to start page number from a custom number (5).

• Go to File Tab ➜ Print ➜ Print Setup ➜ Page.
• In the input box “First page Number”, enter the number from you want to start you page numbers.
• In the end, click OK.

Important Note: This option will only work if you have applied header/footer in your worksheet.

There are some options in Microsoft Excel which are meant to perform a specific function and can help you to get better at Excel in no time...

...and here I have listed some of those tips and tricks which you can use in your daily work.

### 60. Named Range

Data Cleansing Tips

If you work with messy data then these simple tips can help you to clean your data in no time and the best part about these tips is, they are quick to learn.

### 69. Convert to Date

Sometimes you have dates which are stored as text and you can use them in a calculation and further analysis. To simply convert them back to valid dates you can use DATEVALUE function...

### 70. Negative to Positive

Excel Tricks to do with MOUSE

We all are eager to learn more keyboard shortcuts so they can work fast.

But, there are some tricks which we can perform with a mouse as well and these are some mouse tricks which you can use to speed up your work.

### 72. Rename a Worksheet

Let me tell you why this method is faster than using a shortcut. Suppose if want to rename more than one worksheet using shortcut key.

Before you change the name of a worksheet, you need to activate it. But if you use the mouse it will automatically activate that worksheet and edit the name with only two clicks.

### 73. Fill Handle

• Select the cell in which you have a formula or a value which you want to drag.
• Make a double click on the small square box at the right bottom of the cell selection border.

This method only works if you have values in corresponding column and it works only in the vertical direction.

### 74. Hide Ribbon

Just make a double click on the active tab in your ribbon and it will collapse the ribbon. And if you want to expand it back just double click on it again.

### 75. Edit a Shape

Besides the typical method, you can use double click to edit a shape and insert the text into it.

You can also use this method to edit and enter text in a checkbox or into a chart title.

### 76. Column Width

It auto sets the column width according to the column data.

The same method can be used to auto adjust row width.

### 77. Go to the Last Cell

By using a double click, you can go to the last cell in the range which has data.

You have to click on the right edge of the active cell to go to the right side & on the left edge if you want to go to the right side.

### 78. Chart Formatting

All you have to do is just make a double-click on the border of the graph to open formatting option.

### 79. Pivot Table Double Click

As you already know Excel stores data in pivot cache before creating a pivot table.

You can extract data from a pivot table by double-clicking on data values.

As soon as you do this Excel will insert a new worksheet with the data which has used in the pivot table.

### 80. Right Click

To use this menu all you need to do is select a cell or a range of the cell...

...and then right-click and while holding it, drop the selection to somewhere else.

You’ll get a menu with some useful options.

Tips for One Time Set-Up

There are few options in Excel which you can customize according to your need to save your time and efforts on-wards and I have listed some of those here.

### 85. AutoCorrect Option

If you do a lot of data entry in Excel then this option can be a game changer for you.

With the auto correct option, you can tell Excel to change a text string into another when you type it.

Let me tell you an example:

My name is “Puneet” but sometimes people write it like “Punit” but the correct spelling is the first one. So what I can do is, use auto correct and tell Excel to change “Punit” into “Puneet”.

### 86. Custom List

Just think like this, you have a list of 10 products which you sell. Whenever you need to insert those product name you can insert them using a custom list.

### 88. Gridline Color

If you are not happy with the default color of cell grid-lines then you can simply change it with a few clicks and follow these simple steps for this:

This is one of my favorite one-time set up to save time in the long run. The thing is instead of going to start menu to open Microsoft Excel, the best way is to pint it to the task-bar.

### 90. Macro to QAT

Alright, you have a macro code which you need frequently in your work. The thing is you use it run that code from macros or by a shortcut key.

Time Saver Tips

Excel tips are all about saving time and making you more productive. Below we have some time saver tips which you can use.

### 99. Screen Clipping

And for this, Excel has an option which can capture screen instantly, and you can paste it into the worksheet. For this go to ➜ Insert ➜ Illustrations ➜ Screen Clipping.

### 100. Locate a Keyboard Shortcut

This tip is a game changer, yes that’s right. If you are using Excel 2007 to Excel 2016, then you can locate a keyboard shortcut by pressing ALT key.

• Top 25 Options in Excel’s Mobile App (Android & iOS)
• Top 100 Pivot Table Tips and Tricks You Need to Learn
• Top 100 Useful Excel Macro [VBA] Codes Examples
• Top 100 Excel Functions with Examples + Samples Files
• Top 10 Advanced Excel Charts and Graphs to Make You PRO
• How to use INDEX MATCH in Excel

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

### 81 thoughts

1. I really appreciate your post. It helps me a lot. Besides, I like your website’s name “ExcelChamps”

2. I’ve come across a formula which I’m not familiar with – can you help explain it?

=mdValueDL(“TBTypeTotal”,PeriodEndDate,ExcludeList,\$A7,\$B7)

I’m aware that PeriodEndDate and ExcludeList are named ranges and can work out that “TBTypeTotal” is pulling in the total of each of the categories in the range, but I can’t find any literature on the mdValueDL function.

Any ideas?

3. Thanks Puneet
I love some of your tips and example you have put into here, specially the camera tool.
some of the tips were irresistible, I had to read them all…
keep it up

4. Aging Baby Boomer here. I’ve learned ‘the hard way’ about Excel and I use it quit a bit to share information. Your tips are very helpful. You put it in easy to understand language – helps a lot for those of us who have had to learn late in life. Thank you.

7. great work. really useful.
thanks

8. Thank you so much for sharing your knowledge. I was looking at how to convert negative numers to positive and found so much more, appreciated

9. Hi Puneet,

Thank you for what you are doing and I need your help.

I am using excel 2010 but got stuck in having the PowerPivot add-in works. the problem is the PowerPivot is not loading, I’m having a runtime error message during the loading of COM Add-in.

Any tip for a way out?

Thanks again.

10. Thanks a lot my dear, I was able to absorbed this quickly…

Could you please give me the link where I can learn all the functions for the excel and macros .

12. Hi Puneet,

Thank you for being so generous in your knowledge of Excel. we are surely learning a lot from you. keep it up.

• You’re Welcome.

13. Dear Puneet,

Please guide me how to use Hlook up.

14. Great post! I’m really getting to know Excel better and i’m loving it! What an amazing tool!

15. I found this really useful, thank you

• Thanks. 🙂

16. Trying to use vlookup but not working. Any advice?

Thanks,
Ben

• =vlookup(e75,a2:d185,3,0)
I am looking for the exact corresponding third column value within the search range of a2 thru d185.
Vlookup searches only to its left (vlook0op(a75,b2:e185,3,0) will not work.
Oh, the third column/3 means the third column from the right in the range/a2:d185. In this regard it will be in column “C”.
In range =vlookup(e75,b2:e185,3,0), the 3 pulls from column “D”/ B is 1. C is 2. D is 3.
Last effort to explain: =vlookup(r758,f350:h766,2,0) produces whatever is in row 768 from column 2/G.
Hope this helps or gets others to correct “OUR” understanding.

17. Ꮢattling supｅrb info cаn be found onn weƅ
blⲟg . “The fundamental defect of fathers is that they want their children to be a credit to them.” by Bertrand Russell.

18. Very useful Excel tips here, I like all of them. If you’re a beginner obviously all is useful but there are good features for advanced users here that hadn’t know about before.

• I’m glad you liked it.

19. Awesome content… Though small but very useful in day to day life…
Thank you very much Puneet.

• You’re welcome 🙂

20. Thanks a lot for the impressive knowledge you are sharing with us.. I’ll share all of them with my friends. It’s pricelessly appreciated 🙂 🙂 (Y)

• Thanks for your words, Bilal.

21. brilliant one thing how do I move down in a cell when I am typing, if I press return I go to the next cell and I want to stay in the same cell but just start a new sentence.

• You mean a line break you need.

• Alt+Enter