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: 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:
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:
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.
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: 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:
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:
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.
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 PanesThis 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. 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: 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: 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: 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!! 😉 Share your list of top daily troubleshooting tricks you use to get the work done 🙂 Useful Excel Resources:
Useful Excel Interview Questions + Answers. Free Online Excel Training. Free Excel Templates. How to Copy Chart Formatting in Excel
Other Excel tutorials you may also like:
Copy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent)
PLS SOLVE THE PROBLEM
