24 Excel Tricks to Make You Sail through Day-to-day work

Excel has made our life easier in so many ways. However, there are some Excel Issues that continue to waste our time and pump up the frustration levels. These are small issues that can be fixed in a few seconds, but to not know the right way to do it can cost a lot of time.

24 Excel Tricks for Day-to-day Work

In this blog post, let me unveil 24 Excel tricks that will save you a lot of time and energy. If you work with excel, sooner or later you get into trouble with something that seems impossible.

Here are some quick fixes that will make it possible.

Excel Trick #1 – Copy-Paste Formulas without Changing References

It happens all the time. You try to copy a cell with a formula to another cell and it gets all mixed up. This happens because the references change when you copy to another location (unless of course you be strict and fix all the references).

Here is the quick fix:

  1. Select the cells that you want to copy.
  2. Go to Home –> Number. In the Number format drop-down, select Text. This will change the cell’s format to Text.
  3. Press F2 to get into the edit mode. Now hold the Control key and hit Enter.
  4. Copy the cells.
  5. Go to the destination cell and paste.
  6. Change the format from Text to General.
  7. Press F2 and Hit Control + Enter.
  8. And You are Done!!.

Here is another great method by Excel MVP Tom Urtis (using Find and Replace) [credit to Dave Bruns of ExcelJet for showing me this method]

Excel Trick #2 – Fill Handle Does Not Show Up

I see so many people facing this issue. The Fill handle just does not show up for work.

Here is the fix:

  1. Go to File –> Options.
  2. In the Options Dialogue box, select Advanced.
  3. Check the option ‘Enable Fill Handle and cell drag-and-drop’.

Excel Trick #3 – Number as Text

I get stuck with this a lot of times. Numbers are formatted as text and do not work well in formulas.

Here is a quick fix:

  • Add 0 to the cell. This will keep the number intact and can be used in formulas.

A lot of people use the apostrophe (‘) before the number to make numbers as text. This excel trick would work in this case as well.

Excel Trick #4 – Need to Convert Formulas to Values

This is an easy one. If you have formulas that need to be converted to values, here are the steps:

  1. Copy the cells.
  2. Right-click and select Paste Special.
  3. In the Paste Special Dialogue Box, select Values (Keyboard Shortcut – Alt + E + S + V).
  4. Click OK.
See Also: Learn more about Paste Special Shortcuts.

Excel Trick #5 – Cursor Stuck – Not Able to Change Active Cell by Arrow Keys

The first time this happened to me, I closed the Excel workbook and restarted my system. The problem, of course, can be solved easily without restarting.

The issue, in this case, is that you have accidentally activated the Scroll Lock.

Switch it off and get going.

Excel Trick #6 – VLOOKUP/MATCH doesn’t work even when there is a Match

You can see that the 2 values match perfectly, but the VLOOKUP or MATCH formula would still say there is no match.

Argghhhhh…

Extra spaces are the culprits here. If the text has leading or trailing spaces (or more than one space between words), excel would not consider it as an exact match, and your formula would throw up unexpected results.

The way around – use the TRIM function. It removes any leading and trailing spaces, and any extra space between words.

See Also: A lot of issues can be solved if you know how to clean your data in excel.

Excel Trick #7 – Want to Hide Text in Cells

Sometimes you may need to hide text/numbers in a cell so that the user can not see it.

There are 2 ways to do this:

  • The good one: Make the text font color white (or whatever color is the background).
  • The better one: Select the cell and hit Control + 1. This opens the Format Cells dialogue box. In number tab, select Custom and type the format ;;;

Excel Trick #8 – Need to Copy Visible Cells Only

  • Question: What happens when you copy a range of cells which has hidden rows/columns, and paste it somewhere?
  • Answer: All the cell values (Visible + Hidden) get copied and pasted.
  • Concern: I don’t want to copy hidden cell values.
  • Solution: Select the cells and press Alt +; (this selects visible cells only). Now paste anywhere and only visible cells gets pasted.

Excel Trick #9 – Select All Blank Cells in One go

I often import data from various databases. A lot of cells in this data are blank, where I have to insert a 0 or N/A. A quick way to select all blank cells:

  1. Select all the cells in the range.
  2. Go to Home –> Editing –> Find and Select –> Go to Special.
  3. Select Blanks in the Go To Special Dialogue box.
  4. Click OK.

This will select all the blank cells. To enter 0 in all the cells, type 0 (this will enter the value in the active cell) and press Control + Enter.

Excel Trick #10 – Need to Remove Line breaks from Text

If you have a lot of line breaks and you want to remove these line-breaks, doing it manually is going to take a lot of time.

It would be a shame if you have to do this manually.

Try this quick-fix:

  1. Select the data
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H)
  3. In the Find and Replace Dialogue Box:
    • Find What: Press Control + J (you may not see anything except for a blinking dot)
    • Replace With: Space bar character (hit space bar once)
  4. Click on Replace All (make sure word wrap is enabled)

See Also: Read more about amazing things that Find and Replace can do for you.

Excel Trick #11 – Sorting does not Work when Cells are Merged

Of course, it does not.

It wasn’t made that way. But that does not mean you can not have what you want. Here is a substitute for Merge and Center, and it is called Center Across Selection.

Here is the right way to Merge cells in Excel:

  1. Select the cells that you want to merge (columns only. For rows, go ahead and use merge and center).
  2. Press Control + 1 to open Format Cells dialogue box.
  3. Go to Alignment Tab.
  4. In Horizontal drop-down, select Center Across Selection.
  5. Click OK.

Now you have something that looks the same, and works great with Sorting.

Excel Trick #12 – Data has Duplicates

There can be 2 things you can do with duplicate data – Highlight It or Delete It

  • Highlight Duplicate Data: Select the data and Go to Home –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.
    • Specify the formatting and all the duplicate values get highlighted.
  • Delete Duplicates in Data: Select the data and Go to Data –> Remove Duplicates.
    • Select if your data has headers, then select the column and click OK. This removes duplicates from the list. If you want the original list intact, copy-paste the data at some other location and then do this.
Related: The Ultimate Guide to Find and Remove Duplicates in Excel

Excel Trick #13 – Need to Check for Spelling Errors

Press F7 to run spell-check.

Here is a detailed tutorial on using Spell check in Excel.

Excel Trick #14 – Scrolling Down on Large Data Sets hide Column Titles

There are 2 ways to resolve this.

  • Use Excel Table Feature: Using Excel Table feature ensures that when you scroll down, the headers are always visible. As a good practice, Excel Table should always be used with tabular data as it has some very powerful and useful feature. Learn more about Excel Tables
  • Using Free Panes Options: If you want more than the header to be visible (say in case of a sub-heading or values such as total, average at the beginning), use the Excel Freeze Pane feature. To do this:
    • Select the Entire row above which you want to Freeze the Rows (or the leftmost cell in that row)
    • Go to View –> Freeze Panes –> Freeze Panes
      This will enable you to always see the fixed rows when you scroll down.

Excel Trick #15 – Wasting time Scrolling Back and Forth in Large Data Sets

If you work with huge data sets and you have to scroll up and down (or right and left) to refer to some data points, there is a way you can save a lot of time and scrolling – by using the Split Window feature.

This feature can split your window in two or four parts, and each part has its own scrolling enabled. So if you have to refer to data in say column CZ, while you are working in column A, then this feature is perfect for you.

  1. Go to View –> Window –> Split.

This splits the worksheet into four parts. Each part can be controlled by an additional scroll bar (vertical and horizontal). If you only want a vertical split, drag the horizontal split line to the bottom (and it will disappear).

Excel Trick #16 – Debugging Formula

Missed a comma, wrong reference, missing argument, wrong parenthesis position and so on.. There could be hundreds of reasons for a wrong result by a formula.

Debugging a formula could be painful, especially if it has been created by someone else. Here is a way to make it easy and simple. To debug a formula:

  1. Select the cell that has the formula.
  2. Go to Formulas –> Formula Auditing –> Evaluate Formula (Keyboards Shortcut Alt + TUF).
  3. Click on Evaluate to see the steps the formula is evaluated by Excel.

Want to debug part of a formula – Here is another way using the F9 key

Excel Trick #17 – Excel Inserts Cell Reference While Editing a Formula

This is by far one of the most irritating excel issues that I go through almost daily. Imagine you have a huge formula, you press F2 to edit the formula (in a cell or conditional formatting or named range), and as soon as you press left key to edit part of it, Excel inserts some unwanted cell reference. Excel does this sometimes as it does not know that you are in Edit Mode. You can see the current mode at the bottom left of your screen.

The way around is simple, press F2 once to get into the edit mode, and now you can roam around freely in your formula.

Excel Trick #18 – Too many named ranges (do not remember the names)

I use Named Ranges a lot. It has many benefits and it’s easy to use. But what if there are too many Named Ranges?? Would you have to go back to the name manager again and again to get the name?

NO!!

Simply use the shortcut F3. It will display the names of all the named ranges in your workbook. Just double-click on the one you want to use and it will be pasted (in the formula if you making one, or in the sheet)

Question for You? What happens if you have many named ranges, and you decrease the zoom below 40%? I will leave that for you to figure out.

Excel Trick #19 – Error Error Everywhere

An error is one ugly blotch on your spreadsheet. But sometimes, errors are the necessary evil. You may want your formula to return an error (say when a lookup value in not found in VLOOKUP). But you don’t have to go through the travails of staring at one. Simply use IFERROR.

IFERROR would not work for Excel version 2003 and earlier. In that case, use a combination of IF and ISERROR.

Excel Trick #20 – Need to Quickly Delete All Comments

I often re-use templates that have comments. To remove all the comments is one of the worksheet operations that I do whenever I start over. Here is a quick way to select all the comments at one go and delete all:

  1. Select all the cells.
  2. Go to Home –> Editing –> Find and Select –> Go to Special.
  3. Select Comments in the Go To Special Dialogue box.
  4. Click OK.

This will select all the cells that have comments in it. Now go to any of the selected cells, right click and select Delete Comment

Excel Trick #21 –  Switch Between Excel Spreadsheets Only

Are you one of those who open zillions of applications at the same time.

I often have Google Chrome, Excel, PowerPoint, Kindle, Mozilla Firefox, Email, and many more applications open at the same time.

Most of us use Alt + Tab to cycle through different open applications, but what if you want to cycle through open Excel Workbooks only. Use Control + Tab instead.

Excel Trick #22 – Display Number with Text

Want to display the number 100 as 100 Million or 100 Grams? You can do this, and still use this number in calculations. The trick is in knowing the right number format. To display 100 as 100 million:

  1. Go the cell that has the numbers (or select all the cells where you want to apply this).
  2. Press Control + 1.
  3. In the Number tab go to Custom (in the left pane).
  4. Change General to General “Million”.

Custom Number formatting is a delight in itself. Learn some amazing trick using Custom Number Format

Excel Trick #23 – Quickly Scroll to the Right in a Huge Data Set

Suppose you have a huge data set that covers a lot of columns (say 200 columns). And you have to scroll through your data often. What are the ways you can do this.

  • Usual way 1: Use the right arrow key (and cover each column one by one).
  • Usual Way 2: Leave the keyboard, get hold of the mouse, select the scroll bar and scroll.
  • Here is the third not-so-usual way – Use Alt + PageDown Key. It does what Page down does for rows. It jumps 20 columns at a time.

Excel Trick #24 – Suffering from Slow Spreadsheet

We have all been through this. Excel Spreadsheets have a tendency to get too damn slow.

Some steps below may help things speed-up. [I wrote a more detailed article on how to speed up spreadsheets. Click here to read]

  • Use Helper columns.
  • Avoid Volatile formula.
  • Use Manual mode of calculation instead of automatic.
  • Use Excel Tables for housing data.
  • Avoid array formula.

These are my top 24 Excel tricks (in no specific order). I am sure there are hundreds more, but there is only so much a person can write at one go!

Add a Comment

Your email address will not be published.