Top 100 Excel Tips and Tricks Which will Make You a PRO in 2018

Top 100 Excel Tips and Tricks Which will Make You a PRO in 20182018-07-03T10:18:14+00:00

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 thing is…

You must have a list which you can refer every day instead of searching here and there.

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.

Yes, it is.

And all the tips and tricks are listed under a specific category which makes it easy to follow the entire list.

Basic Tips

Even if you don’t want to be a power Excel user there are few basic tips you can learn to save your time in daily work and here are the top 10 basic tips which you can learn.

1. Add Serial Numbers

excel tips tricks to add serial numbers

2. Insert Current Date and Time

excel tips tricks insert current date time

3. Select Non-Adjacent Cell

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 to add serial numbers

4. Sort Data Quickly

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 they are 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. Move Data with 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 thing which you can check using status bar.

excel tips tricks status bar

Some of my favorite things are numeric count, the maximum value and minimum value.

7. Open 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 paste all the values which you have copied in past.

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

excel tips tricks clipboard copy paste

8. Insert Bullet Points

  • Press Ctrl + 1 and you will get a format dialog box.
  • Under 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 the cell Excel will add a bullet before that.

excel tips tricks insert bullet points

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

All you have to do is select the worksheet tab and press and hold CTRL and drag and drop after the last tab.

10. Undo-Redo Button

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 the most time-consuming thing 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 Option

  • First of all, you need to add it to the quick access toolbar (here are the steps).
  • After that, whenever you need to apply formatting, just select the data where you want to add 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 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

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 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 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. Add a 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 Data Tab ➜ Data Tools ➜ Data Validation ➜ Data Validation.
  • In the data validation window, go to 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. Apply 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.

Control + 5

strike through excel tips tricks

…and if you are using MAC then here the shortcut.

Control + 5

Quick Note: You can use the same shortcuts keys if you need to do this for partial text in a cell.

15. Bar-Code Font

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

16. Show a Date as a Month/Year

Alright, let’s say you have a date in a cell and you want that date to show as a month or a year. And for this, you can apply custom formatting to do this.

  • 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

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

For example, if you want to use Green color for positive numbers and red color for negative numbers then you need to use custom format.

excel tips and tricks to red green negative positive numbers

[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. Create a Custom Font/Color 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 style of 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.

  • Once you are done with your formatting, go to Page layout ➜ Tab Themes ➜ Themes ➜ Save Current Theme.
  • It opens a “Save As” dialog box, name your theme and save it.
excel tips and tricks to red green negative positive numbers add custom formatting
  • 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 Home Tab.

Home Tab ➜ Editing ➜ Clear Clear ➜ Formats.


Formula Tips

21. Convert a Text Case to 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

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

23. Count Words

=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 into it after that which equals to the total number of words in a cell.

24. Calculate Person’s Age

And, the formula will be:

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

Make sure to replace “Date-of-Birth” with your actual date of birth.

25. Calculate Ratio

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

26. Calculate N Root of Number

=number^(1/n)

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

=625^(1/2)

excel tips tricks using an exponent operator formula to get square root

27. Total Days in Month

=DAY(EOMONTH(TODAY(),0))

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

This always gives you the total number of days using the present date as a base.

28. Get Month’s last Date

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

excel tips and tricks to end of the month date

29. VLOOKUP to Left

As we all know there is no way to lookup to left for a value using VLOOKUP. But if you switch to INDEX MATCH you can look up in any direction.

30. SUMPRODUCT IF

You can use below formula to create a conditional SUMPRODUCT and product values using a condition.

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

Charting Tips

Charts are the most ignored part of Microsoft Excel. But, to make you better at it, 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 in Line Chart

If you love to use 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:

  1. Select data line in your chart and right click on it.
  2. Select “Format Data Series”.
  3. Go to Fill & Line ➜ Line ➜ Tick mark “Smoothed Line”.
excel charting tips smooth line chart tick mark

32. Copy and Paste Chart Formatting

excel tips tricks copy chart formatting from one chart to another using paste special
  • Select your chart which you have formatted & copy it.
  • Now, select the chart where you want to paste that formatting.
  • Go to Home Tab ➜ Paste ➜ Paste Special ➜ Formats.
  • Click OK.

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

34. Change Display Units in a Chart

If you are dealing with the large number in your chart, you can change the units for your axis values.

excel tips tricks to hide axis label before after

Here are the steps:

  1. Select chart axis your chart and open format “Format Axis” options.
  2. In axis options, go to “Display Units” where you can select unit for your axis values.

This will help your users to understand the chart quickly.

35. Round Corner Chart

I often use excel charts with rounded corners and if you like to use round corners in your chart 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

36. Hide Empty Gap in Chart

Let’s say if you have a chart with monthly sales in which Jun has no amount and cell is empty. You can use following options for that empty cell.

  1. Show gap for the empty cell.
  2. Use zero.
  3. 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).

37. Insert Picture in Excel Chart

excel charting tips add picture in excel bar chart

Here I’m using a bar chart to add an image and the steps are:

  • Select data bar in your chart.
  • Go to Fill & Line and in “Fill” option, select picture and texture fill.
  • In “Insert Picture From” click on file to select an image.
  • Select “Stack” after that.

38. Save a Chart Template

This is also a time saver tip.

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

...steps are as follow.

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

39. Change Default Chart Type

Again, an insane time saver charting tip.

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 line chart, then the shortcuts 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:

  1. Go to Insert Tab ➜ Charts.
  2. Click on the arrow at the bottom right corner.
  3. Then in your insert chart window, go to “All Charts” and then select chart category.
  4. Right click on the chart style which you want to make your default Select “Set As Default Chart”.
  5. Click OK.

40. Show Data for Hidden Cells

When you hide a cell from the data range of your chart, it will also hide from the chart as well.

And if you want not to hide data values from your chart even a cell is hidden, just follow these steps.

  • Select your chart and right click on it.
  • Go to ➜ Select Data ➜ Hidden and empty cells.
  • From the pop-up window, tick mark “Show data in hidden rows and columns”.

Now your chart will remain same even if any cell from your data range is hidden.

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

Let’s say you headings in your table and you want to print those headings on every page you print. In this case, you can fix “Print Titles” to print those headings on each page.

Here's how to do this:

  • Go to “Page Layout Tab” ➜ Page Set Up ➜ Click on Print Titles.
  • Now in the page setup window go to sheet tab and specify following things.
excel tips tricks print title goto
  1. Print Area: Select the entire data which you want to print.
  2. Rows to repeat at the top: Heading row(s) which you want to repeat on every page.
  3. Columns to repeat at the left: Column(s) which you want to repeat at the left side of every page (if any).
excel tips tricks print title select
  • In the end, click OK.

Now the row and column you have selected will repeat for each page.

42. Page Order

Specifying page order is quite useful when you want to print large data. All you need to do:

  • Go to File Tab ➜ Print ➜ Print Setup ➜ Sheets Tab.
  • Now here, you have two options:
    • The First Option: To print your pages using vertical order.
    • The Second Option: To print your pages using horizontal order.

43. Printing Comments

If you use comments in your reports then you can print them while printing reports. Using this option, at the end of the all printed pages, you’ll get a list of all the comments.

Here are the steps:

  • Go to File Tab ➜ Print ➜ Print Setup ➜ Sheets Tab.
  • In print section, select “At the end of the sheet” using comment drop down.
  • Click OK.

44. Scale to Fit

Sometimes we struggle to print entire data on a single page. In this situation, you can use “Scale to Fit” option to adjust the entire data into a single page.

  • Go to File Tab ➜ Print ➜ Print Setup ➜ Page Tab.
  • Next, you need to adjust two options:
  1. First, adjust using a % of normal size.
  2. Second, specify the number of pages in which you want to adjust your entire data using width and length.
excel tips tricks scale to fit
  • Click OK.

Now you can take a print out on a single page.

45. Custom Header-Footer

Instead of using page numbers in header and footer, you can also use custom header and footer. Here’s how to do this:

  • Go to File Tab ➜ Print ➜ Print Setup ➜ Header/Footer.
  • Click on custom header or footer button.
  • Here you can select alignment of the header/footer.
  • And following options can be used:
  1. Page Number
  2. Page Number with total pages.
  3. Date
  4. Time
  5. File Path
  6. File Name
  7. Sheet Name
  8. Image
  • In the end, click OK.

The best use of this option is for adding copyright text (Just a thought).

46. Center on Page

Let’s say you have fewer data to print on a page. In this case, you can align them with the center of the page while printing.

Follow these steps:

  • Go to File Tab ➜ Print ➜ Print Setup ➜ Margins.
  • In “Center on Page” you have two options to select.
  1. Horizontally: Aligns data to the center of the page.
  2. Vertically: Aligns data to the middle of the page.
  • Click OK.

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

Right?

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.

  • Select the range of the cells which you want to set as a print area.
excel tips tricks select range to print
  • Go to Layout Tab ➜ Page Set Up ➜ Print Area ➜ Set Print Area.
  • Once you do that, that range will get highlighted with a gray border.

And if you want to remove that printing area, just go back to the option and select “Clear Print Area”. You can also select more than one printing area.

All you have to do is just select both of the ranges and set them as a print area.

Important Note: If you have more one print area in your worksheet excel will print them on different pages.

48. Custom Margin

You know what I mean.

  • Go to File Tab ➜ Print.
  • Once you click on print, you’ll get an instant print preview.
  • Now, from the bottom right side of the window, click on”Show Margins” button.
excel tips tricks show custom margins

It will show all the margins applied and you can change them by just drag and drop.

49. Change Cell Error Values

The thing is, you can replace all the error values while printing with another specific value. Well, you have only three other values to use as a replacement.

Here are the steps:

  • 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 print it on a page.

50. Start Page Number with a Custom Number

If you want to start page number from a custom number (5). You can specify that number and rest of the pages will follow that sequence.

  • 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.
excel tips tricks+start page numbers from custom page
  • 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 with Watch Window

  • First of all, go to Formula Tab ➜ Formula Auditing ➜ Watch Window.
excel tips tricks use watch window
  • Now in “Watch Window” dialog box, click on “Add Watch”.
excel tips trick add a cell to watch window
  • After that select the cell or range of cells which you want to add and click OK.

52. Extract data with Flash Fill

Flash fill is one of my favorite options to use in Excel. It’s like a copycat, perform the task which you have performed.

…let me give you an example.

excel tips tricks to use flash fill

You have dates in the range A1:A10 and now, you want to get the month from the dates in the B column.

All you need to do is to type the month of the first date in the cell B1 and then come down to cell B2 and press the shortcut key CTRL + E.

Once you do this it will extract the month from rest of the dates, just like below.

53. Combine Multiple Worksheet into One

I’m sure somewhere in the past you have received a file from your colleague where you have 12 different worksheets for 12 months data.

In this case, the best solution is to combine all of those worksheets using “Consolidate” option and here are the steps for this.

  • First of all, add a new worksheet and then go to Data Tab ➜ Data Tools ➜ Consolidate.
excel tips tricks combine worksheets
  • Now in the “Consolidate” window, click on the upper arrow to add the range from the first worksheet and then click on the “Add” button.
  • Next, you need to add references from all the worksheets using above step.
excel tips tricks add reference combine worksheets
  • In the end, click OK.

54. Password Protect a Workbook

Adding a password to a workbook is quite simple, here are the steps.

  • While saving a file when you open a “Save As ” dialog box go to Tools General Options.
  • Add a password to for “Password to Open” and click OK.
excel tips tricks enter password
  • Re-enter the password and click OK again.
  • In the end, save the file.

Now, whenever you re-open this file it will ask you to enter the password to open it.

55. Capture a Live Image of a Range

  • Select the rage you want to paste as an image and copy it.
  • Go to the cell and right click, where you want to paste it.
  • Go to Paste Special ➜ Other Paste ➜ Options Linked Picture.
excel tips tricks create live image with paste special

56. In-Build Data Entry Form

A few of the Excel users know that there is a default data entry form is there which we can use. And the best part is there is no need to write a single line of code for this.

excel tips tricks default data entry form

Here’s how to use it:

  • First of all, make sure you have a table with headings where you want to enter the data.
  • After that select any of the cell from that table and use the shortcut key Alt + D + O + O to open the user form.

57. Create a Custom Tab in the Excel Ribbon

We all some favorite option or some options which we use frequently. To access all those options in one place you create tab and add them to it.

Follow these steps:

  • First of all, go to File Tab ➜ Options ➜ Customize Ribbon.
excel tips tricks new tab
  • Now click on “New Tab” (this will add a new tab).
  • After that right click on it and name it and then name the group.
  • Finally, we need to add options to the tab and for this go to “Choose Commands From” and add them to the tab one by one.
  • In the end, click OK.

Now you are a new tab in the Excel ribbon with all the favorite options.

58. Goal Seek

In simple words, Goal Seek is a problem-solving tool. It helps you find the input value by proving the value you want in the result.

excel tips tricks goal seek

59. Text to Speech

60. Quickly Create a Named Range

To create a named the range the easiest method is to select the range create it using “Create from Selection” option.

Here are the steps to do this:

  • Select the column/row for which you want to create a named range.
  • Right click and click on “Define name...”.
excel tips tricks right click select define name
  • Select the option to add name for the named range and click OK.
excel tips tricks enter name and click ok

That's it.

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 Function

TRIM(text)

All you need to do is refer to the cell from where you want to remove leading and trailing spaces and it will return the text without those extra spaces.

excel tips tricks remove extra spaces with trim

62. Remove Duplicates

One of the most common thing which we face while working with large data is “Duplicate Values”. In Excel, to remove these duplicate values is quite simple.

Here’s how to do this.

  • First of all, select any of the cells from the data or select the entire data.
  • After that, go to Data ➜ Data Tools ➜ Remove Duplicates.
excel tips tricks remove duplicates
  • At this point, you have “Remove Duplicates” window and from this window, select/de-select the columns which you want to consider/not consider while removing duplicate values.
excel tips tricks remove duplicates window
  • In the end, click OK.

Once you click OK, Excel will remove all the rows from the selected data where values are duplicate and show a message with the number of values removed and unique values left.

63. Merge Text by using Fill Justify

I know five different way merge text from a range but out of those Fill Justify is my favorite.​

It’s one of the less used options in Excel, but worth not to be missed for any reason.

Let say you have words in the range A1:A5 and you want to concatenate all of them in a single cell.

Here’s how to do this with fill justify.

excel tips tricks combine text with fill justify
  • First of all, make column A enough wide so that the entire text can be combined into one cell.
  • After that, select the entire range.
  • Now, go to Home Tab ➜ Editing ➜ Fill ➜ Justify.

Boom! it combined the text from range A1:A5 to cell A1.

64. Remove a Specific Character using Find and Replace

Let’s say you have some text values in a column and from those values you want to replace a specific character or a word, you can do this simply by find and replace option.

  • All you need to do is select that column and open the find and replace dialog box.
  • After that click on “Replace” tab.
excel tips tricks find and replace
  • Now here, in “Find What” enter the character you want to replace and make sure to leave “Replace with” blank.
  • Now click on “Replace All”.

The moment you click on “Replace All” Excel will remove that particular character from the entire column.

65. Combine Text from Multiple Cells

Alright, you have text in multiple cells and you want to combine all the text into one cell.

No, this time not with fill justify. We are doing it with a formula.

If you use Office 365, there is new function TEXTJOIN which is a game changer when it comes to the concatenation of text.

excel tips tricks text join

Here’s the syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

All you need to do is to add a delimiter (if any), and TRUE if you want to ingnore empty cells, and in the end refer to the range.

66. Unpivot Data with Power Query

67. Select and Delete all the Error Cells in a Worksheet

Mostly while working with large data it obvious to have error values but it’s not good to keep them. The easiest way to deal these error values is select them and delete them and these are the simple steps.

  • First of all, go to Home Tab ➜ Editing ➜ Find & Replace ➜ Go To Special.
  • In the Go To dialog box, select formula and tick mark errors.
excel tips tricks select errors
  • In the end, click OK.

Once you click OK it will select all the errors and then you can simply delete all by using “Delete” button.

68. Arrange Columns

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

excel tips tricks convert text to date

70. Convert Negative Number into Positive

excel tips tricks convert negative number into positive with abs

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. Apply Cell Format with Format Painter

Apply Cell Formatting With Double Click Mouse Tricks
  1. Select the cell or a range from where you want to copy cell formatting.
  2. Go to ➜ Home Tab ➜ Clipboard.
  3. Now, make a double click on “Format Painter” button.
  4. As soon as you do this, your cursor will convert into a paintbrush.
  5. Now, you can apply that formatting anywhere in your worksheet, in another worksheet or even in another workbook.

72. Rename a Worksheet

I always found it quicker than using a shortcut key to change the name of a worksheet.  All you have to do is just double click on the sheet tab and enter a new name.

Rename Worksheet name With Double Click Mouse Tricks

Let me tell you why this method is fast 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. Double Click on Fill Handle

I am sure shortcut addicts always use a shortcut key to drag formulas and values in downward cells. But using fill handle is more impressive than using a shortcut key.

  1. Select the cell in which you have a formula or a value which you want to drag.
  2. 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 columns and it works in the vertical direction.

74. Collapse or Expand the Ribbon

If you want to work in a distraction-free mode, you can do this by collapsing your Excel ribbon.

excel tips tricks to collapse expand 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

We often use shapes in our worksheets to present some messages and we have to insert some text into those shapes.

Besides from the typical method, you can use double click to edit a shape and insert the text into it.

excel tips tricks edit a shape

You can also use this method to edit and enter text in a checkbox or into a chart title.

76. Adjust Column Width

Whenever you have to adjust column width you can double click on the right edge of the column header. It auto sets the column width according to the column data.

excel tips tricks adjust column width

The same method can be used to auto adjust row width.

77. Go To the Last Cell of the Data

This trick can be useful if you are working with a large dataset. By using a double click, you can go to the last cell in the range which has data.

excel tips tricks go to last cell

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. Open Chart Formatting Options

If you use Control + 1 to open formatting options to format a chart, then I bet you’ll love this trick.

All you have to do is just make a double-click on the border of the graph to open formatting option.

excel tips tricks open chart formatting options

79. Get Source Data from a Pivot Table with Double Click

Let’s say someone sent you a pivot table without the source data.

As you already know Excel stores data in pivot cache before creating a pivot table.So you don’t need data source until you have to make any changes in data.

excel tips tricks get source data 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 Menu

There is a right-click drop-down menu in Excel which few users know about. To use this menu all you need to do is select a cell or a range of the cell.

excel tips tricks right click menu

And, then right-click and while holding it drop the selection to somewhere else. There are quite useful options which you can use.

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. Change Default Location

Normally while working on Excel I create more than 15 Excel files every day. And, if I save each of these files to my desktop it looks nasty.

To solve this problem, I have changed my default folder for saving a workbook and here’s how to do this.

  • First of all, go to file tab and open Excel options.
excel tips and tricks to change default location click on options
  • In Excel options, go to “Save” category.
  • Now, there is an input bar where you can change the default local file location.
  • From this input bar, change the location address and in the end, click OK.

From now onwards when you open “Save As” dialog box you’ll it will show you the location you have specified instead of the default location.

82. Disable Start Screen

I’m sure just like me you hate when you open Microsoft Excel (or any other Office app) and you see the start pop-up screen. It takes time depending on your system’s speed and add-ins you have installed.

Here are the steps to disable start-up screen in Microsoft Office.

  • First of all, go to file tab and open Excel options.
  • In Excel options, go to “General” category.
  • From option, drill down to “Start-Up” options and untick the “Show the Start screen when this application starts”.
excel tips and tricks to disable start screen
  • In the end, click OK.

From now onward, every time when you start Excel it will directly open the workbook without showing the start-up screen.

83. Show Developer Tab

Before you start writing VBA codes the first thing you need to do is enable “Developer Tab”.

When you first install any version of Microsoft Excel developer wouldn’t be there so you need to enable it from settings.

And here the steps form this:

  • First of all, go to File tab and click on “Customize Ribbon” category.
  • Now from the tab list, tick marks the developer tab and click OK.
excel tips and tricks to activate developer tab

Now when you come back to your Excel window you’ll have developer tab on the ribbon.

84. Enable Macros

If you don’t macros and VBA codes then I’m sure you have macros enables which are disabled by default.

The thing is when you open a macro-enabled file you need to enable macro options to run VBA codes and here the steps to this:

  • First of all, go to File tab and click on “Trust Center” category.
  • From here click on “Trust Center Settings”.
  • Now in “Trust Center Settings”, click on macro settings.
  • After that, click on “Enable all macros” and tick mark “Trust access to the VBA project object model”.
excel tips tricks create enable macros trust center
  • In the end, click OK.

Now you can run VBA codes without any restriction.

85. Adding a 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:

  • First of all, go to File tab and click on “Quick Access Toolbar” category.
excel tips tricks add macro to qat
  • After that, from “Choose Command from”, select Macros.
  • Now select the macro (you want to add to QAT) and click on add.
  • From here click on “Modify” and select an icon for the macro button.
  • In the end, click OK.

Now you have a button on QAT which you can use to run the macro code you have just specified.

86. 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 to use auto correct and tell Excel to change “Punit” into “Puneet”. Here’s how to do this:

  • First of all, go to File tab and go to options and click on “Proofing” category.
  • After that, click on “AutoCorrect Option” and this will open auto correct window.
excel tips tricks auto correct
  • Here in this window, you have two input bars to specify the text to replace and text to replace with.
  • Enter both of the values and click OK.

87. 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 insert them using a custom list. Let me tell you how to do this:

  • First of all, go to File tab and go to options and click on “Advanced” category.
  • Now drill down and go to “General” section and click on “Edit Custom List…”.
  • Now in this window, you can enter the list or you can also import it from a range of cells.
  • In the end, click OK.

Now to enter the custom list you have just created, enter the first entry of the list in cell and then drill down that cell using fill handle.

88. Applying Table to Data

This is one the million dollar tip. If you use pivot tables a lot then it’s important to apply table to the raw data.

With a table, there is no need to update pivot table’s data source and it drag-down formulas automatically when you add a new entry.

excel tips tricks create apply table

To apply table to the data just use Ctrl + T keyboard shortcut key and click OK.

89. Change Grid-line Color

If you are not happy with the default color of cell grid lines then you can simply change it with few clicks and here’s how to do this:

  • First of all, go to File tab and click on “Advanced” category.
  • Now go to “Display options for this workbook” section and select the color you want to apply.
  • In the end, click OK.

90. Pin to Taskbar

This is one of my favorite one-time set up to save time in long run. The thing is instead of going to start menu to open Microsoft Excel, the best way is to pint it to the taskbar.

This way you can open it by clicking on the icon from the taskbar.

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 All the Formula Cells

  • In “Go To Special” dialog box, select formulas and click OK.
excel tips tricks select formula cells

Once you click on this it will instantly select all the cells with formulas.

92. Multiply using Paste Special

To do some one-time calculations you can use paste special option and save yourself from writing formulas.

excel tips tricks multiply paste special

93. Highlight Duplicate Values

Here are the steps you need to follow:

  • First of all, select the range from where you want to highlight the duplicate values.
  • After that, go to Home Tab ➜ Styles ➜ Highlight Cells Rule ➜ Duplicate Values.
excel tips tricks highlight duplicate values conditional formatting
  • Now from the dialog box, select the color to use and click OK.

Once you click OK, all the values which are duplicate will get highlighted.

94. Quick Analysis

If you ever noticed that when you select a range of cell in Excel, a small icon at the bottom of the selection appears.

This icon called “Quick Analysis”.

When you click on this icon you can see some of the options which are there on the ribbon which you can directly use from here to save you time.

95. Open Excel using RUN Command

Yes, you can also open your Excel application using RUN command.

For this, all you have to do is open RUN (Window Key + R) and then type “excel” into it.

excel tips tricks open with run command

In the end, hit enter.

>