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

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.

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.

excel tips tricks to add serial numbers

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

excel tips tricks insert current date 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.

excel tips tricks select non adjacent cells without holding ctrl key

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

excel tips tricks sort buttons qat

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.

excel tips tricks move data with drag and drop

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.

excel tips tricks 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.

excel tips tricks click board open from home tab

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

excel tips tricks clipboard copy paste

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

excel tips tricks custom formatting for bullet points
  • Click OK.

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

excel tips tricks insert bullet points

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.

excel tips tricks create worksheet copy

10. Undo-Redo

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

excel tips tricks undo redo buttons

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

excel tips tricks use 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.

excel tips tricks use format painter
  • 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”.
excel tips tricks create cell message
  • In the end, click OK.
excel tips tricks create cell message display

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

strike through excel tips tricks

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

15 excel tips tricks install barcode font

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.
16 excel tips tricks show date as month year
  • 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.
excel tips and tricks to highlight blank cells select data
  • After that, go to Home Tab ➜ Editing ➜ Find & Select ➜ Go To Special.
excel tips and tricks to highlight blank cells click goto special
  • From Go To Special dialog box, select Blank and click OK.
excel tips and tricks to highlight blank cells click blank option
  • At this point, you have all the blank cell selected and now apply a cell color using font settings.
excel tips and tricks to highlight blank cells apply color

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.
excel tips and tricks to red green negative positive numbers add custom formatting
  • 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.

excel tips tricks clear formatting

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.

21. Sentence Case

excel tips and tricks to convert to sentence case

22. Random Numbers

excel tips tricks rand
excel tips tricks rand between

23. Count Words

excel tips and tricks to count words

24. Calculate Age

excel tips and tricks to calculate age

25. Ratio

excel tips tricks to use simple divide to calculate ratio in excel

26. Root of Number

27. Days in Month

excel tips tricks get total number of days in a month using formula

28. Month’s last Date

excel tips and tricks to end of the month date

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.

31. Smooth Line

excel tips tricks charting tips smooth line chart
excel charting tips smooth line chart tick mark

32. Chart Formatting

excel tips tricks copy chart formatting from one chart to another using paste special

33. Hide Axis Labels

excel tips tricks to hide axis label

34. Display Units

excel tips tricks to hide axis label before after

35. Round Corner

excel tips tricks round corner chart setting tick mark

36. Hide Gap

37. Insert Picture

excel charting tips add picture in excel bar chart

38. Chart Template

excel charting tips save as template right click
excel charting tips save as template apply chart

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.
excel charting tips change default chart

40. Hidden Cells

excel charting tips show hidden value

Printing Tips

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

So many times we need to adjust custom margins, scaling, or adding header and footers.

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

41. Print Titles

excel tips tricks print title goto
excel tips tricks print title select

42. Page Order

excel tips tricks set printing direction

43. Print Comments

44. Scale to Fit

45. Custom Header-Footer

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.

Advanced Tips

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.

51. Tracking Important Cells

52. Flash Fill

53. Combine Worksheets

54. Protect a Workbook

55. Live Image

56. Userform ?

57. Custom Tab

58. Goal Seek

59. Text to Speech

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.

61. Trim

62. Remove Duplicates

63. Merge Text

64. Remove Specific Character

65. Combine Text

66. Unpivot Data

67. Delete Error Cells

68. Arrange Columns

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.

71. Format Painter

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. 

81. Default Location

82. Disable Start Screen ?

83. Developer Tab

84. Enable Macros

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.

87. Apply Table

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:

89. Pin to Taskbar

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.

But the easiest way to run a macro code is to add it to the quick access toolbar. Follow these simple steps:

excel tips tricks add macro to qat

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.

91. Select Formula Cells

93. Highlight Duplicate Values

94. Quick Analysis

95. RUN Command

96. Open Specific File

97. Open Excel Automatically

C:\Users\Puneet Gogia\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup

98. Smart Look Up

99. Screen Clipping

Sometimes you need to add screenshots into your spreadsheet.

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 Excel Tutorials

  • 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

About the Author

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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

78 thoughts

Leave a Comment

Your email address will not be published.

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

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

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

  4. Thank you for uploading the files for excel tips.
    Could you please give me the link where I can learn all the functions for the excel and macros .

  5. Hi Puneet,

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

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

  6. Ꮢattling superb 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.

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

  8. 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)

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

  10. Hi Puneet,
    Hope you doing great
    I am working on smart excel dashboard.
    Need some help in automation part but need to connect with for discussing
    Please help

  11. Thanks puneet. It will be much help if you will provide 1 sheet with multiple macro n their work explained in other sheet

  12. I’ve got this on my reading list. Can someone tell me whether these are pertinent to Windows or Macintosh or both?