One of the FASTEST ways to Learn Excel is to learn some of the Excel TIPS and TRICKS, period and if you learn a single Excel tip a day you can learn 30 new things in a month.
But you must have a list that you can refer to every day instead of search here are there.
Well, I’m super PROUD to say that this is the most comprehensive list with all the basic and advanced tips that you can find on the INTERNET.
In this LIST, I have covered 100 Excel TIPS and TRICKS which you can learn to Level Up your Excel Skills.
Let’s start this learning fun…
Top Excel Tips and Tricks (Category Wise)
- Basic Tips
- Formatting Tips
- Formula Tips
- Charting Tips
- Printing Tips
- Advanced Tips
- Data Cleansing Tips
- MOUSE Tricks
- One Time Set-Up Tips
- Time Saver Tips
Important: Don’t forget to download this e-book from here covering all of these tips and tricks in one PDF.
1. Serial Numbers
If you work with large data then it’s better to add a serial number column to it. For me, the best way to do this is to apply the table (Control + 5) 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 and you’ll get the serial number.
2. Current Date and Time
The best way to insert the current date and time is to use the NOW function which takes date and time from the system and returns it.
The only problem with this function is it’s volatile, and whenever you recalculate something it updates its value. And if you don’t want to do this, the best way is to convert it to hard value.
You can also use the below VBA code.
Sub timeStamp() Dim ts As Date With Selection .Value = Now .NumberFormat = "m/d/yyyy h:mm:ss AM/PM" End With End Sub
Or these methods to insert a timestamp in a cell.
3. Select Non-Adjacent Cell ★
Normally we all do it this way, hold the control key and 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 just by selecting them.
4. Sort Buttons
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.
5. Drag and Drop
How to move data from one section of your worksheet to another? I’m sure you think about copy-paste but you can also use drag-drop for this.
Simply select the range 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 are a lot of options you can add.
7. Paste from 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 and you can paste values from there.
8. Add Bullet Points
The easiest way to insert bullet point in Excel is by using custom formatting and here are the steps for this:
- Press Ctrl + 1 and you will get the “Format Cell” dialogue box.
- Under the number tab, select custom.
- In the input bar, enter the following formatting.
● General;● General;● General;● General
- Click OK.
Now, whenever you enter 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.
10. Undo-Redo Buttons
Just like sort buttons you can also add undo and redo buttons to the QAT. The best part about those buttons is you can use them to undo up to a particular activity without pressing the shortcut key again and again.
1. Auto Format
If you deal with financial data then auto format can be one of your best tools. It simply applies the format to small as well as large data sets (especially when data is in tabular form).
- First of all, you need to add it to the quick access toolbar (here are the steps).
- After that, whenever you need to apply the format, just select the data where you want to apply it and click on the AUTO FORMAT button from quick access toolbar.
- It will show you a window to select the formatting type and after selecting that click OK.
The AUTOFORMAT is a combination of six different formattings and you have the option to disable any of them while applying it.
2. Format Painter
The simple idea with the format painter is to copy and paste formatting from one section to another.
Let’s say you have specific formatting (Font, Cell Color, Bold, Border, etc.) in the range B2:D7, and with format painter, you can copy that formatting to range B9: D14 with a click.
- First of all, select the range B2:D7.
- 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 B9: D14 .
The format painter is fast and makes it easy to apply to format from one section to another. Even you can also apply formatting multiple times, you can read more about it from here.
3. Cell Message
Let’s say you need to add a specific message to a cell, like “Don’t delete the value ”, “enter your name” or something like that.
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.
Unlike Word, in Excel, there is no option on the ribbon to apply strikethrough.
But I have figured out that there are 5 ways to do it and easiest from all of them is a keyboard shortcut.
All you need to do it select the cell where you want to apply the strikethrough and use the below keyboard shortcut.
Control + 5
And if you are using MAC then:
⌘ + ⇧ + X
Quick Note: You can use the same shortcut keys if you need to do this for partial text.
It’s one of those secret tips which most of the Excel users are unaware.
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.
6. Month Name
Alright, let’s say you have a date in a cell and you want that date to show as a month or a year. For this, you can apply custom formatting.
- 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.
7. 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.
- 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.
…but you can also use conditional formatting for this
8. Font Color with Custom Formatting
In Excel, we can apply custom formatting and in custom formatting, there is an option to use font colours (limited but useful).
For example, if you want to use the Green colour for positive numbers and red colour for negative numbers then you need to use the custom format.
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 the “Custom” category and the custom format in the input dialogue box.
- In the end, click OK.
9. Theme Color
We all have some favourite fonts and colours which we use in Excel. Let’s say you received a file from your colleague and now you want to change the font and colours for the worksheet from that file. The point is, you need to do this one by one for each worksheet and that takes time.
But if you create a custom theme with your favourite colours and fonts then you can change the style of the worksheet with a single click. For this, all you have to do is apply your favourite designs to the tables, colours to the shapes and charts, font style and then save it as a custom theme.
- Go to the Page Layout Tab ➜ Themes ➜ Save Current Theme. It opens a “Save As” dialogue 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.
10. Clear Formatting
This is a simple keyboard shortcut that 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).
1. Sentence Case
In Excel, we have three different functions (LOWER, UPPER, and PROPER) to convert a text into different cases.
But there is no option to convert a text into sentences case. Here is the formula which you can use:
This formula converts the first letter of a sentence into capital and rest of all in small…
2. Random Numbers
In Excel, there are two specific functions which you can use to generate random numbers. First is RAND which generates random numbers between 0 and 1.
And second is RANDBETWEEN which generates the random numbers within the range of two specific numbers.
ALERT: These both functions are volatile so whenever you re-calculate your worksheet or hit enter they update their values so make sure to use them with caution.
You can also use RANDBETWEEN to generate random letters as well.
3. Count Words
In Excel, there is no specific function to count words.
You can count characters with LEN but not words. But, you can use the following formula which can help you to count words from a cell.
This formula counts the number of spaces from a cell and adds 1 to it after that which equals the total number of words in a cell.
4. Calculate Age
The best way to calculate a person’s age is by using the DATEDIF function. This mysterious function is specifically made to get the difference between two dates.
And the formula will be:
=”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).”
I have figured out that there are 4 different ways to calculate ratio in Excel but using a simple divide method is the easiest one.
All you need to do is divide the larger number into the smaller one concatenate it with a colon and one and here’s the formula you need to use:
This formula divides the larger number with the smaller so that you can take the smaller number as a base (1).
6. Root of Number
To calculate square root , cube root or any root of a number the best way is to use exponent formula. In exponent formula, you can specify the Nth number for which you want to calculate the root.
For example, if you want to calculate a square root of 625 then the formula will be:
7. Days in Month
To get a total number of days in a month dynamically you can use the following formula:
8. Month’s last Date
To simply get the last date of a month you can use the following dynamic formula.
9. INDEX MATCH
As we all know there is no way to look up to left for a value using VLOOKUP. But if you switch to INDEX MATCH you can look up in any direction.
10. SUMPRODUCT IF
You can use the below formula to create a conditional SUMPRODUCT and product values using a condition.
1. Smooth Line
If you love to use a line chart then you are awesome but it would be more awesome if you use a smooth line in the chart.
This will give a smart look to your chart.
Here are the steps:
- Select the data line in your chart and right click on it.
- Select “Format Data Series”.
- Go to Fill & Line ➜ Line ➜ Tick mark “Smoothed Line”.
2. Chart Formatting
3. Hide Axis Labels
This charting tip is simple but still quite useful. If you don’t want to show axis label values in your chart you can delete them.
But the better way is to hide them instead of deleting. Here are the steps:
- Select Horizontal/Vertical axis in the chart.
- Go to “Format Axis” Labels.
- In label position, select “None”.
And again if you want to show it then just select “Next to axis”.
4. Display Units
If you are dealing with the large numbers in your chart, you can change the units for axis values.
Here are the steps:
- Select chart axis your chart and open format “Format Axis” options.
- In axis options, go to “Display Units” where you can select unit for your axis values.
5. Round Corner
I often use Excel charts with rounded corners and if you like to use round corners too, here are the simple steps.
- Select your chart and open formatting options.
- Go to Fill and Line ➜ Borders.
- In borders sections, tick mark rounded corners.
6. Hide Gap
Let’s say if you have a chart with monthly sales in which Jun has no amount and cell is empty. You can use the following options for that empty cell.
- Show the gap for the empty cell.
- Use zero.
- Connect data points with the line.
Here are the steps to use these options.
- Right, click on your chart & select “Select Data”.
- In select data window, click on “Hidden and Empty Cell”.
- Select your desired option from “Show Empty Cell as”.
Make sure to use “Connect data points with the line” (recommended).
7. Pictograph in Excel
8. Chart Template
Let’s say if you have a favorite chart formatting which you want to apply every time you create a new chart. You can create a chart template to use it anytime in the future and steps are as follow.
- Once you have done with your favorite formatting, right click on it & select “Save As Template”.
- Using save as dialog box, save it in the template folder.
- To insert a new with your favorite template, select it from templates in insert chart dialog.
9. Default Chart
You can use a shortcut key to insert a chart, but the problem is, it will only insert the default chart, and in Excel, the 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. Here are the steps to fix 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.
10. Hidden Cells
When you hide a cell from the data range of a chart, it will also hide that data point from the chart as well. To fix this, just follow these steps.