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

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…

Table of Content Open

Top Excel Tips and Tricks (Category Wise)

  1. Basic Tips
  2. Formatting Tips
  3. Formula Tips
  4. Charting Tips
  5. Printing Tips
  6. Advanced Tips
  7. Data Cleansing Tips
  8. MOUSE Tricks
  9. One Time Set-Up Tips
  10. Time Saver Tips

Important: Don’t forget to download this e-book from here covering all of these tips and tricks in one PDF.

Basic Tips

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.

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

excel tips tricks insert current date time

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.

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

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.

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.

excel tips tricks move data with drag and drop

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.

excel tips tricks status bar

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.

excel tips tricks click board open from home tab

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

excel tips tricks clipboard copy paste

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:

  1. Press Ctrl + 1 and you will get the “Format Cell” dialogue box.
  2. Under the number tab, select custom.
  3. In the input bar, enter the following formatting.

● General;● General;● General;● General

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

Now, whenever you enter 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.

excel tips tricks create worksheet copy

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.

excel tips tricks undo redo buttons

Formatting Tips

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

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.

excel tips tricks use format painter
  • 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”.
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.

4. Strikethrough

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

strike through excel tips tricks

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.

5. Barcode

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.

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.

learn more about this tip from here

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

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

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

[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 the “Custom” category and the custom format in the input dialogue box.
excel tips and tricks to red green negative positive numbers add custom formatting
  • 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.

Steps:

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

excel tips tricks clear formatting

Formula Tips

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:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
excel tips and tricks to convert to sentence case

This formula converts the first letter of a sentence into capital and rest of all in small…

…learn how this formula works

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.

excel tips tricks rand

And second is RANDBETWEEN which generates the random numbers within the range of two specific numbers.

excel tips tricks rand between

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.

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

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.

excel tips and tricks to calculate age

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

5. Ratio

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:

=Larger-Number/Smaller-Number&”:”&”1″

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

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.

=number^(1/n)

For example, if you want to calculate a square root of 625 then the formula will be:

=625^(1/2)

7. Days in Month

To get a total number of days in a month dynamically you can use the following formula:

=DAY(EOMONTH(TODAY(),0))
excel tips tricks get total number of days in a month using formula

8. Month’s last Date

To simply get the last date of a month you can use the following dynamic formula.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
excel tips and tricks to end of the month date

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.

=SUMPRODUCT(–(C7:C19=C2),E7:E19,F7:F19)

Charting Tips

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.

excel tips tricks charting tips smooth line 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

Copy Chart Format in Excel

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”.
excel tips tricks to hide axis label

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.

excel tips tricks to hide axis label before after

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.
excel tips tricks round corner chart setting tick mark

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

How to Create a 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”.
excel charting tips save as template right click
  • 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.
excel charting tips save as template apply chart

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

 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.