This is the largest collection of best excel tips and tricks on the web. I know, you have got a lot of advice out there about various excel tips and tricks, but you still tend to crave for more, aren’t you?
The reason is very simple.
Microsoft excel is not just a piece of software, it’s a skill. Burning Glass research suggests that “nearly 8 in 10 middle-skill jobs require digital skills like Spreadsheet & word processing”.
Source: Burning Glass Research
Excel is a very powerful tool packed with thousands of options.
In fact, you have everything that you might need, but You & I have to admit that we only know very few functionalities.
So, in this in-depth tutorial, we are going to take a look at Best Excel tips and tricks to boost productivity by 5x [atleast].
and not just that…
…at the end of this tutorial, you have an option to download our free excel tips Ebook.
Excited right..?
let’s dive in.
Best Excel Tips and Tricks: ‘A definitive guide’
#1. Save workbooks in ‘Excel Binary‘ format
If there is one single most important thing that you need to know in excel, that would be ‘Excel binary format‘.
When you save workbooks in binary format, your data will be compressed and the size will reduce drastically. This would prevent possible crashes due to file size.
Another notable feature is its compatibility, in case if you were to write & run some Macros in your workbook then the ‘binary’ format is your ideal option.
In fact, I did some comparison to prove it factually correct, the results are mindblowing.
#2. Hassle free formatting with Ctl+1
Ctl+1 is the only shortcut key that you should know about all your formatting requirements.
You may classify this as a beginner excel tips and tricks, but trust me this excel tip is for everyone.
You can pretty much do everything from Number formatting to Alignment, Font to Protection & Borders to Colours, you name it.
If you can master six tabs in ‘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 sum formula for multiple Columns/Rows. With the help of this function, you can insert a formula into multiple cells with a single click.
“One single click can make the difference – Excel Auto Sum”
You can refer our simple guide to help you get started. Alternatively, you can also use excel keyboard shortcut‘Alt =’ [Alt+Equal] for the same.
#4. Customize your Quick Access Toolbar [QAT]
QAT is a customizable toolbar as per the user requirement. You can add most used options like Autofilter, 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 filter icon added to QAT and then start using
Tip: If you press Alt key you can see number shortcut assigned to it
#5. Format Painter
Format Painter is often considered as one of the best excel tips. With the help of little tiny painting brush, you can copy formatting from one cell to another cell easily.
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.
Tip: when you click on ‘format painter’ you’ll see little brush icon around mouse pointer
#6. Use wildcards to find & replace data
Using wildcards to find something in your workbook is a little bit tricky and considered as an advanced excel trick. 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
When you copy content from a cell you will most likely copy text as well as formatting, however, in many cases you just need text.
So, to get rid of unwanted formatting simply use excel paste special option by pressing Alt+E+S+V to paste values.
#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 below steps.
#9. Breakdown lengthy formulas
This is one of my favourite excel spreadsheet tips.
It’s quite evident that we often write lengthy excel formulas to execute complex logic’s, but as you know it’s very hard to debug in case if you have any errors.
So, the best and easiest way is to break down formulas by each logic by using Alt+Enter in the formula bar. With the help of below example, you can see yourself the benefits breaking formulas.
One of our Commission calculation formulae will look like below before and after using this technique:
I guess by this time you would have understood the relevance of using formula breakdown method.
#10. Quick filter technique
There is no doubt, we use excel filters every day. But, I bet 9 out of 10 will not know quick filter technique.
With the help of quick filter technique, you can filter values from your active cell with the help of right-clicking. You can refer below example on how to filter ‘Central’ region by using right-click.
#11. Sort data before working on large files
In fact, we often use large chunks of data for our analysis, during the process, you would have noticed excel reacts strangely. Some well-known symptoms are Excel will crash & your system will slow down.
Indeed, it’s disgusting.
Trust me with the help of simple excel Sort function you can speed up your analysis. When you apply Sort, your random data will be saved in a systematically sorted order, which results into faster access.
#12. Named ranges can add a lot of value
Named ranges play a significant role in the financial modelling 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 it in formulas. Indeed, no one likes machine language i.e A1: B1, instead, you can name it according to your requirement.
You can access name manager from, Formulas → Name Manager.
#13. Differentiate Input cells & Formula cells with a colour coding
When we talk about best excel tips and tricks, colour coding takes centre stage. It’s 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 colours across the workbook.
You may refer below example for further understanding.
#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 Control + Enter combination, you can quickly fill formulas in a selected range.
For example, if you want to add current date in A1: A5 range:
Select range A1: A5 → enter Today() formula → Click on Control+Enter
#15 Never ever hard code your formulas
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 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.
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
Boom, now you’ll see only formatting like what we have here.
#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 a vertical you can follow simple steps.
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
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 colour freaks. The best workaround for this is to start using built-in styles.
In case if you don’t like built-in styles, better create your own style and use it in your workbook. It’s just 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 a 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.
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?
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 best excel tips and tricks, we certainly need to pay attention to Excel calculation options and it’s usage.
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
#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.
You can disable 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 useless blank space between words.
And the worst part is you can’t identify quickly.
So, use 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 modelling 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.
#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 IFERROR formula, it’s very simple yet effective. Please refer below example for further understanding.
#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 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.
#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 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, MID formula can help you extract middle value from a text.
For example, to extract 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:
#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:
#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 below examples.
#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 type of errors using a built-in option like below.
#38 Trace precedents
One of the best feature 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.
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:
#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.
#41 Boost your analytical skills with SUMPRODUCT formula
I’m sure most of us don’t use 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 below example for further understanding:
#42 Don’t scroll Up & Down, use Freeze Panes
Scrolling up & down will ruin your time.
Instead, you can use freeze Panes option to make your headers constant.
You can find this option under → View → Freeze Panes
#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 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 feature but extremely powerful excel tip.
You can systematically organize your workbook data by grouping together.
To access this option navigate to Data → under Outline → Group or Ungroup
#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, few very handful excel shortcuts are essential to get going.
You may refer our guide on keyboard shortcuts:
#46 Commenting is essential – yet we ignore
We often work with a bunch of excel formulas to get the things done.
Indeed, it works.
But, what if you have to revisit to 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.
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 ‘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.
#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 ‘A to Z & Z to A Sorting’ method.
But, you have got a lot of sorting options, like Sort by font colour, 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.
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 user can edit/formula cells that you want to hide from view. Follow simple steps to hide formulas.
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.
