Top Excel Tips and Tricks (101) – Basic + Advanced + Free PDF to Make You a PRO this Year

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

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.

excel tips tricks select non adjacent cells without holding ctrl key

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

excel tips tricks sort buttons qat

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.

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

excel tips tricks undo redo buttons

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


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

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1
excel tips and tricks to count words

24. Calculate Age

excel tips and tricks to calculate age


=”Your age is “& DATEDIF(Date-of-Birth,Today(),”y”) &” Year(s), “& DATEDIF(Date-of-Birth,TODAY(),”ym”)& ” MONTH(s) & “& DATEDIF(Date-of-Birth,TODAY(),”md”)& ” Day(s).”


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


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



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

MOUSE Tricks

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.