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
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
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.
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.
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.
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 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.
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.
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.
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.
- In the end, click OK.
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.
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
22. Random Numbers
23. Count Words
24. 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).”
26. Root of Number
27. Days in Month
28. Month’s last 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 ?
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
32. Chart Formatting
33. Hide Axis Labels
34. Display Units
35. Round Corner
36. Hide Gap
37. Insert Picture
38. Chart Template
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
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
42. Page Order
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.
- Double minus sign.
- “#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.
51. Tracking Important Cells
52. Flash Fill
53. Combine Worksheets
54. Protect a Workbook
55. Live Image
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.
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
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:
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. Once you press it, it shows the keys for the options which are there on the ribbon, just like below.
About the Author
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.