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 GUIDE – BEST 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.
#2. Hassle free formatting with Ctl+1
Ctl+1 is the only shortcut key that you should know for all your formatting requirements.
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.
#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
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.
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
#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.
#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.
#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.
#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.
#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.
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.
#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.
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
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.
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.
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.
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.
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 color 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 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.
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 the 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
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.
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.
#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.
#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:
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:
#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:
#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 to 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 types of errors using a built-in option like below.
#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.
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 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:
#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
#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:
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
#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:
#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.
#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.
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.
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.
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.
Icons credit: flaticon.com