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 theunder the clipboard section [refer below image]*Format Painter option***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

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.

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.

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:

=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:

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

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:

## #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:

**=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

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

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*

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.

David – That’s a good one.

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.