51 Best Excel Tips and Tricks to skyrocket your productivity [2021 update]

5

So, you want to learn various excel tips & tricks to up your productivity game? if so, you have landed in a right place.

There has been so much written about the best excel tips and tricks on the internet, I could literally find 2,23,000 exact matches by the time of writing this article.

So, I don’t want to write one more article with all the garbage.

Instead, I have selected these tips based on my last 10 years of corporate experience as an analyst.

Well, who uses excel more than a financial analyst? so, let’s dive in…!!


DEFINITIVE GUIDEBEST EXCEL TIPS AND TRICKS


Before I start, just one quick research fact about why you should learn Excel and moreover how these excel tips and tricks will pay you in long term.

According to Burning Glass Research, “MS office & Productivity tools” ranked second in a list of flagship skills


#1. Save workbooks in ‘Excel Binary‘ format


If there is one single most important thing that you should know in excel, I think that would be Excel binary format‘.

When you save your workbook in binary format, your data will be converted into binary form.

As a result, the size of your workbook will reduce drastically, so this will prevent possible crashes due to file size and improve file loading speed.

Another notable feature is its compatibility.

In case if you want to write macros in your workbook then the binary format works very well.

In fact, I did some comparisons to prove it factually correct, the results are very impressive.

Excel Binary file type - XLS vs XLSB

#2. Hassle free formatting with Ctl+1


Ctl+1 is the only shortcut key that you should know for all your formatting requirements.

paint-brushes

You may classify this as a beginner excel tip but trust me it works for everyone.

You can pretty much do everything from Number formatting to Alignment, Font to Protection & Borders to Colors, you name it.

If you can master six tabs in the format cells dialogue box then you’re pretty much covered.


#3. Improve your speed by using Autosum


Autosum is another handy feature when it comes to adding the SUM formula for multiple Columns/Rows.

With the help of this function, you can insert a formula into multiple cells with a single click.

Here is an example of how to get started with excel auto sum functionality.

Alternatively, you can also use the excel keyboard shortcut  Alt+Equal for the same.

Excel Auto Sum

#4. Customize your Quick Access Toolbar [QAT]


QAT is a customizable toolbar as per the user requirement. You can add your most-used options like Auto filter, Subtotal, Paste Special, etc. to QAT, and then you can access them quickly either by mouse or by using a shortcut from QAT.

For example, if you want to add Autofilter to QAT you can follow simple steps as per below.

Step-1: Navigate to Data → Filter

Step-2: Right-click on Filter option → Select Add to Quick Access Toolbar

Step- 3: Now locate the filter icon added to QAT and then start using

best excel tips and tricks - quick access toolbar

Tip: If you press the ALT key you can find out shortcut key for each feature.


#5. Format Painter


Format Painter is one of my favorite excel tips, it makes our life so much easy to replicate formatting across multiple cells.

With the help of a little tiny painting brush, you can copy formatting from one cell to another cell easily.

brushes

When you click once on the icon you can apply formatting to a single cell, in case if you want to apply for multiple cells then you need to double-click on the icon.

How to use Format Painter:

  • Step1: Select your cell or a group of cells from which you want to copy formating
  • Step2: Click on the Format Painter option under the clipboard section [refer below image]
  • Step3: Then just place your mouse cursor and click on your destination cell – that’s it
Excel format painter

#6. Use wildcards to find & replace data


You can use wildcard characters in excel to find information quickly.

Very often people think this is very difficult and only for advanced users, but the fact is anyone can use it and it works great.

You can use * [Asterisk] to find out any number of characters followed by a specific alphabet.

Alternatively, you can use “? [Question Mark]” for a single character.

For example, if you want to find employee names starting with “Na” you can use Na* you will see results like Nagendra, Nadish, Nakul, etc.

In case if you want to find out names starting with N & ending with M you can search like n?????????m.

 


#7. Everything is special with Paste Special


Paste Special is a very unique and useful feature.

Suppose when you want to copy any content from one cell or sheet to another then you are not only copying the content but also the underlying components like formatting (Color, Font, Comments, etc.)

In most cases, you just need the content, not the formatting.

So, to get rid of unwanted formatting simply use the Excel paste special option by pressing Alt+E+S+V to paste values.

Or just follow the below guide.

Best excel tips and tricks - excel paste special

#8. Arithmetic operations with Paste Special


Do you know excel paste special option will support simple arithmetic operations like addition, multiplication, division & subtraction?

I often use these to convert negative numbers to positive with a single click. You can follow the below steps.

It just works great.

Paste Special with operations

#9. Breakdown lengthy formulas


This is one of my favorite excel spreadsheet tips.

It’s quite evident that we often write lengthy excel formulas to execute complex logics, but as you know it’s very hard to debug in case of any errors.

So, the best and the easiest way is to break down formulas by each logic by pressing Alt+Enter in the formula bar.

With the help of the below example, you can see yourself the benefits of breaking down formulas into various lines.

As you can see below the first formula without breaks looks bit difficult to read and understand but the second one looks much better.

So, just remember this excel tip for your next complicated formula.

Formula breakdown in excel

#10. Quick filter technique


There is no doubt, we use excel filters every day.

But I bet 9 out of 10 will not know the quick filter technique that I’m about to show you.

As you can see below, I’m trying to filter data by electronic accessories.

So instead of using top filter I just need to right click on electronic accessories and then click on Filter and then Filter by Selected Cells Value.

Now the data will be automatically filtered based on your cell value.

Excel tips & tricks - Quick filter in Excel

#11. Sort data before working on large files


Large excel files can become headache very quickly, it will freeze, it will crash and all kind of issues may crop up.

But you can avoid these issues with a simple excel tip i.e. sorting data before you start working on any excel files.

sort

It works great, I use this excel tip all the time.

In general, when your data is sorted then excel can apply all the logics in a batch as a result faster processing time.


#12. Named ranges can add a lot of value


Named ranges play a significant role in the financial modeling world. It’s often considered as one of the cool excel tricks.

With the help of named ranges, you can assign descriptive names to your ranges and then you can use them in formulas. Indeed, no one likes machine language i.e. A1: B1, instead, you can name it according to your requirement.

You can access the name manager from, Formulas → Name Manager.

 


#13. Differentiate Input cells & Formula cells with a colour coding


When we talk about the best excel tips and tricks, color coding takes center stage. It’s a very basic yet most important excel tip for both beginners & advanced users.

The basic idea is to separate data input cells & formula cells in a report or model by defining and applying unique colors across the workbook.

You may refer to below example for further understanding.

 Color coding example in excel

#14 Control+Enter can solve formula fill problem


Dragging formulas from one cell to another cell is a cool excel tip, but it’s not always advisable. With the help of the CTL+ ENTER combination, you can quickly fill formulas in a selected range.

paint-roller

For example, if you want to add a current date in A1: A5 range:

Select range A1: A5 → enter Today() formula → Click on Control+Enter


#15 Never ever hard code your formulas


h2o

Admit it!

Hard coding excel formulas will solve your short-term problems, but in a long run, they are extremely harmful, and the worst part is they are very hard to identify.

Here is an example of hardcoded formula.

=A1*1.2%

Just imagine, what will happen if the tax rate changes from 1.2% to 1.5%.

Hence, it’s always a good idea to write formulas that can have cell references.


#16 Hide cell contents in Conditional Formatting


There is no doubt, Conditional formatting plays an important role in data visualization.

hide

But..

Unless you know how to hide content from the formatting.

In this example, I’ve used simple data bar formatting to visualize data like below.

Step #1 Copy B2: B5 data and paste it in C2: C5 → Select C2: C5 → Go to Conditional formatting → Data Bars → Choose the one which you like.

Step #2 Again select C2: C5 → Click on Ctrl+1 → Select Custom format → in the ‘type’ field enter ;;;  → Click ok

Hide cell contents in Conditional Formatting

Boom, now you’ll see only formatting like what we have here.

Also read: Building Excel heat maps using Conditional formatting


#17 Use Transpose to transform your data


Often messy data will make you struggle a lot.

But with the help of Excel transpose option, you can pretty much bend your data according to your requirement.

For example, if you want to convert horizontal values as vertical you can follow simple steps.

How to use excel transpose option

Select & Copy A2: D2 → Click on a blank cell → Alt+E+S+V  or Paste special → Select values → enable Transpose check box → Click ok


# 18 Learn CVZY shortcuts to save 90% of your time


CVZY is a cool excel trick, believe me, excel won’t function without using these. In fact, my Microsoft excel tips article isn’t complete without these!

I know you started guessing, but they are:

Ctr+c → Copy

Ctr+v →  Paste

Ctr+z → Undo

Ctrl+y → Redo

 


#19 Avoid using Merge & Center


It’s ok to use Merge & Center option for basic needs, but beyond that please try to avoid.

The main functionality of this option is to combine two or more cells to accommodate complete data.

But, that comes with a lot of issues. The most problematic part is cell reference while working with Vlookup.


#20 Beautify your reports with structured formatting


text icon

It really matters how you present your data in workbooks. If you just focus on results with some confusing formulas, then your hard work can quickly go unnoticed.

I’d certainly recommend spending few additional minutes to beautify your workbook. Having said that, please don’t overdo that, there is a high chance that your workbook may slow down

 


#21 Use built-in styles for quick formatting


As I mentioned above, formatting is important to present your data.

The funny thing is if you start working on formatting you will tend to spend a lot of time on that because we are color freaks. The best workaround for this is to start using built-in styles.

Excel builtin formatting

In case if you don’t like built-in styles, better create your own style and use it in your workbook. It’s just a one-time exercise but works like a champ!


#22 Use Transpose formula for live updates


Using paste special to transpose data is one way, and then another way is to use a built-in transpose formula. If you have live data to be transposed, then the formula will work well.

For example, in below table salesman & sales columns can be converted to horizontal like below.

Excel transpose formula example

As you can see below, Transpose formula is surrounded by flower brackets, which essentially means it’s an array formula. You need to press Control+Shift+Enter


#23  Find & Select (or GoTo special) to identify all the formulas in a worksheet


Do you know with a single click you can find out all the formulas in a worksheet?

binoculars

Yes, It’s just one click.

Well, with the help of Find & Select option you can do that, here is our guide.

Head over to Home tab → Click on Find & Select → Click on Formulas → that’s it, now you can see all the formulas in your worksheet would have been selected.


#24 Be careful with volatile formulas


Using volatile formulas like Today, Rand & Randbetween will have a bigger impact on your workbook performance.

The reason is very simple.

These type of excel formulas will tend to change as and when your workbook refresh happens, and it results in higher processing time.

So, limit using these formulas in your reports/financial models to a minimum.

 


#25 Calculation Options can help as well as ruin your work


When we talk about the best excel tips and tricks, we certainly need to pay attention to Excel calculation options and it’s usage.

excel calculation options

By default, it will be ‘Automatic’ but you can always change that to ‘Manual’ to stop calculation until you refresh/save your workbook.

But, you should be very careful while using ‘Manual’, it can produce incorrect results due to pausing on calculation.

You can access from Formulas → Calculation options → Automatic/Manual

Also read: 10 Reasons for Excel Formulas not Working 


#26 Get rid of Gridlines


The is no doubt, gridlines are good for basic reporting.

But, in excel dashboards gridlines aren’t necessary. Your Dashboards will not look clean with the gridlines, hence I’d recommend disabling them.

How to disable gridlines in excel

You can disable it from → View → Show/Hide → Uncheck Gridlines checkbox

Best excel tips and tricks based on formulas:


#27 Use TRIM formula to remove unwanted spaces


Unwanted spaces will make you struggle a lot. Most of your formulas will break because of the useless blank space between words.

And the worst part is you can’t identify quickly.

So, use the TRIM formula to remove unwanted spaces in a text like below:

=TRIM(“Hello    Good  Morning”) = Hello Good Morning


#28 Extract Day, Month & Year from a date


In a typical financial modeling environment, you sometimes need to split days and Months from a full date.

For example, to split day, month & year from Aug/27/2016, you can use three simple date formulas like below.

Day_month_year_formulas example

#29 Measure length of a string with LEN formula


LEN is a simple formula to check the length of a text string. This formula will help you identify total letters/words/symbols/spaces in a sentence.

Here is an example:

=LEN(“Best Excel Tips and Tricks”) will return 26, which means we have 26 items in our text string.

Len formula alone will not help you much, but when you combine with other formulas, results will be amazing.


#30 Use IFERROR formula for error management


How awkward it is to have #N/A, #VALUE, #DIV/0! in complex financial models.

The good news is, there is a way to manage these errors using the IFERROR formula, it’s very simple yet effective. Please refer to below example for further understanding.

Iferror formula example

 


#31 Right formula can extract text from right side


I often use the Right formula for various reasons. With the help of this formula, you can extract text from the right side of the text.

For example, to extract the last name Perry from Walter Perry you can use a Right formula like below:

=RIGHT(“Walter Perry”,5)

I have written an advanced tutorial on how to extract names in excel using various excel formulas, please refer.

Also read: 19 profound ways to bend your data


#32 Left formula to extract text from Left side


The functionality of Left formula is very identical to Right, the only difference is, the text will be extracted from the left side.

For example, to extract the first name Walter, you can use a left formula like below:

=LEFT(“Walter Perry”,6)


#33 MID formula – extract middle name


Similar to Right & Left, the MID formula can help you extract middle value from a text.

For example, to extract the middle name ‘Anthony’ from Mark Anthony Fernandez, you can write a formula like below:

=MID(“Mark Anthony Fernandez”,6,8)


#34 Dynamic Right & Left formulas


When you deal with huge amounts of data, regular Right & Left formulas may not help.

But.. if you combine other simple formulas like LEN & FIND and make it dynamic then they work flawlessly.

You can refer below examples:

dynamic-right-left-formula-min

#35 Use SUBSTITUTE formula to replace text


There is no best excel tips and tricks list without SUBSTITUTE formula.

As the name suggests, you can replace part of the text or full text using the above formula based on a condition.

For example, to replace underscore between first and last name with space, we can write a formula like below:

substitute-formula-example

#36 Make use of Upper, Lower & Proper functions to play with the text


There is no better way to handle data in excel other than using text functions:

For example, to convert text from Lowercase to upper case, you can use Upper formula, similar to that you can use the Lower formula for lower case.

And, in case if your data is not in a proper format like jumbled with capital & small letters, you can use the Proper function: refer to below examples.

upper-lower-proper-formulas-new

 


#37  Be careful with circular reference errors


Circular reference errors are the most annoying ones in Excel.

The worst part is, you don’t get to see them quickly, but there is a way to identify these types of errors using a built-in option like below.

circular-reference-example

#38 Trace precedents


One of the best features is to audit formulas as quickly as possible in excel.

With the help of this, you can quickly check which formula is dependent on which cells.

trace-precedents

Place cursor on a formula cell → Formulas → under formula auditing → Click on Trace Precedents 

That’s all, now you will see a blue arrow pointing all the precedent cells for a formula.


#39 Trace Dependents


This feature is exactly the other way around to Trace Precedents.

With the help of this, we can easily trace the dependent cells based on the selected cell. Here is an example to help you understand further:

trace-dependents-example

#40 Take full advantage of Sumif & Sumifs


Financial Modeling is incomplete without Sumif & Sumifs formulas.

Be prepared to answer questions like, what are the total sales from South region? what was it during last year same quarter etc.

With the help of simple Sumif & Sumifs formulas, you can do pretty much everything.

Excel Sumif & Sumifs example

Also Read: Excel SUMIFS Formula: Your Business Can’t Live Without


#41 Boost your analytical skills with SUMPRODUCT formula


I’m sure most of us don’t use the SUMPRODUCT formula very often.

Let’s be honest, we don’t use it because we don’t know how to use it [trust me, I was also on that list]. Please refer to below example for further understanding:

Excel Sumproduct example

#42 Don’t scroll Up & Down, use Freeze Panes


Scrolling up & down will ruin your time.

Instead, you can use the freeze Panes option to make your headers constant.

You can find this option under → View → Freeze Panes

Excel Freeze panes option

#43 Generate Random numbers using Randbetween formula (Caution)


With the help of Randbetween formula, you can generate random numbers very quickly. The good news is, you can specify your upper & lower limits.

For example, If you have to generate 4 digit numbers, you can write a formula like the below:

=RANDBETWEEN(1000,9999)

Caution: Please make sure to convert Randbetween formula cells to values, otherwise the numbers will change every time when you save/refresh your workbook.


#44 Use ‘Group’ feature to organize your workbook


Perhaps grouping is one of the very old excel features but an extremely powerful excel tip.

You can systematically organize your workbook data by grouping them together.

To access this option navigate to Data → under Outline → Group or Ungroup

Excel Group & Ungroup example

#45 Be a keyboard user


After all, timing is something very important.

Sending reports after the deadline is almost equal to not sending.

You can save a lot of time by using keyboard shortcuts, you may not have to learn all of them but certainly, a few very handful excel shortcuts are essential to get going.

You may refer our guide on keyboard shortcuts:

excel shortcut commands by numbers

#46 Commenting is essential – yet we ignore 


We often work with a bunch of excel formulas to get things done.

Indeed, it works.

But, what if you have to revisit your calculations after a week or a Month?

It’s disgusting! because you forgot what you had worked on and sadly you don’t have any notes to refer to.

That sounds like familiar incident right?

To avoid this, you can simply use the built-in comment feature to document your manual adjustments, important points & supporting notes, etc.

You can insert comment by right clicking on a cell → Selct Insert Comments option


#47 “Prepare for Tsunami” – Always backup your workbooks


Excel is your Friend as well as Foe.

Excel is your best friend, based on the rich functionalities to get things done, at the same time it can quickly ruin your hard work in a way of crash or corrupt workbooks.

So, the best practice is to adopt a backup method.

I personally use the ‘Version control method’, trust me it’s very simple.

Just do Save as after major changes and add suffix as v1, v2 v3…etc to the file name.

Excel backup method

#48 Quick filter check


Sometimes it’s a bit difficult to check whether the filter applied in your workbook or not. Often you may have tried completely taking off the filter for confirmation.

But, you can quickly check by referring left side row numbers.

In case the filter is on, then you will see row numbers are highlighted in Blue (similar to a hyperlink).

 


#49 Use custom sort


Most of you limit yourself to the ‘A to Z & Z to A Sorting’ method.

But, you have got a lot of sorting options, like Sort by font color, sort by fill colour & sort by icons etc.


#50 Spell check works in Excel too


It’s quite evident that spell checks integrated very well into MS Word than Excel.

Spell check in excel

But, you can still use the same feature in excel as well.

You can access spell check feature from Review → Spelling, alternatively, you also can use F7 shortcut key.


#51 Don’t let someone ruin your hard work – Protect your formulas


Formula protection is a key aspect of financial modelling, it’s important to safeguard some of your critical formulas.

You can do this by simply defining which cells users can edit/formula cells that you want to hide from view. Follow simple steps to hide formulas.

Lock formula cells

Step#1: Control+A to select entire sheet range → Click on format cells → Go to protection tab → make sure to disable ‘Locked’ & ‘Hidden’ checkboxes → Click ok.

Step#2: Select a formula cell → Right Click → Format cells → Protection → enable ‘Locked’ & ‘Hidden’ checkboxes → Click on ok.

Step#3: Go to Review Tab → Click on Protect sheet → If you want you can provide Password, if not click on Ok.

That’s it.


Conclusion:


We deliberately did not include Vlookup, Match & Index formulas in our best excel tips and tricks list.

I feel these two formulas are very broad to cover, thus it’s good to have a separate tutorial than a paragraph. I have already written an in-depth tutorial on Vlookup you may refer that link.

Now, it’s your turn! do you think I have missed any of your important excel tips?  If so, please comment below. 

Credits:
Icons credit: flaticon.com

Download our free 51 excel tips Ebook

About the author

Sudheer Tammini

Hello,
I am an 'Excel enthusiast' and early adopter of technology. I’d like to learn new things and solve the most difficult business problems using technology (trust me, it's fun). Besides running this website, I’m a full-time employee and have a Master’s degree in Finance.

Subscribe to our: Newsletter
Know more: About Me

6 comments

  • I think backup to a thumb drive, since your whole computer can crash. I think the easiest way is to use directories to designate what to backup.

  • This is excellent, thank you! Another great one that is one of my favorites is Ctrl F4 to repeat the same procedure again.

    • Martha – Indeed ‘repeat’ option is very useful one!, thanks for your comment!

  • Another tip I found usefull :
    use View > New Window + View > Arrange All to compare 2 sheets of the same file next to each other.

    I also love flash fill (an unknown feature who can save a lot of time)

    • Hi Anne – I didn’t know the first one I’ll try and let you know. Indeed flash fill is a great option, but you should have excel 2013 and above to use that.