The following Excel® tips are widely applicable to reducing time on business projects, whether they are finance- or IT-related. It’s never too late to learn a couple of new tricks, and one of them just might wind up saving you hours of precious time on your next project. I recently had a seasoned IT consultant tell me that one of the maneuvers I’m about to show you was “the best thing he has learned in all his years of consulting.” You may already know some of these, but if not, it might be worth it to bookmark this page, or at least print it out and file it away somewhere.
This article is outlined with the easy tips you might already know up front, and we end with the all too elusive “Go To Special”. If you are working with spreadsheets that contain thousands, or even millions, of rows of data, then the “Go To Special” information will prove to be a helpful tool if you need to change a large or small amount of that data based on any kind of logical behavior. We’ll focus on 7 different tips, none of which involve macros or Visual Basic whatsoever – they are purely ground-level functions and features of Microsoft Excel.
The CONCATENATE function can be used to combine any number of strings to form a single string. For example, =CONCATENATE(“Hello ”, “World”) will return the string Hello World (notice the space included after “Hello” in the first argument needed to make the formula return “Hello World” rather than “HelloWorld”). The correct syntax of CONCATENATE is =CONCATENATE(text1, text2, text3, etc.), but it will also work with a referenced cell as one of the arguments, combining the value of that cell with the remaining elements.
Sometimes when you copy a formula from one cell to another cell in a spreadsheet, you want to reference the same cell values, and sometimes you want the formula to be applied to different cell values. For example, when you total one column, and then want to total a second column, you copy the sum formula to the second column and it will sum up the values in the same range of the second column. However, if you want to take a specific percentage in cell B1 and you want to apply it to formulas in multiple places on the sheet, then you will need to use a “$” before the B and a “$” before the 1 ($B$1) in order to keep the referenced column and row constant.
The F4 key is the best way to apply the “$” to cells when you want a formula to reference the same column or row (or both) regardless of where you want to use it on the sheet. For example, if you are referencing cell B1 with a formula in a different cell (let’s call it D8), you can tap F4 up to four times after you click cell B1 in order to be presented with the following options:
1. $B$1
2. B$1
3. $B1
4. B1, back to where you started
(Technically, you can cycle through these four flavors forever, but they will continue to repeat themselves in the above order.)
If a “$” comes before the 1 (B$1) it means that you can fill (or copy and paste) into the cells above or below the cell the formula is in (remember, it’s D8), and the formulas in the new cells will still reference B1. If the “$” wasn’t there, the formula in the cell one below D8 (which is D9) would reference B2, D10 would reference B3, D11 would reference B4, and so on, since the “$” is not there to hold 1, the row value, constant. A similar thing happens in the horizontal direction if the “$” precedes the letter value, in this case B ($B1). Furthermore, If a “$” precedes both the letter and number cell components, that cell will remained fixed in both directions ($B$1). The pictures below use the CONCATENATE (meaning combine) formula to illustrate what happens when you fill to the down and to the right of cell D8, in the order of the options 1 – 4 in the list above.
The examples below combine the CONCATENATE function with a referenced cell. Each contains just two concatenated elements, one text and the other a referenced cell. Because one is a referenced cell, you will be able to see how the “$”s affect what value is returned based on the relative position of the cells.
i. The Original: B1 – Both the row and column values are relative to the location of the cell that the formula is in.
ii. $B$1 – Holds the referenced value in column B and row 1 constant.
iii. B$1 – The referenced row is always row 1, while the referenced column varies depending on the location of the cell the formula is in.
iv. $B1 – The referenced column is always column B, while the referenced row varies depending on the location of the cell the formula is in.
Grouping sheets (also referred to as “tabs”) together allows you to perform the same task on multiple worksheets at the same time – no repetition necessary. For example, if you are constructing a workbook that will have 10 sheets, all with the same values in the top row (most likely column labels), then grouping the sheets together will save you some time by allowing you to type the column labels only one time. There are two ways to accomplish the grouping, and they are situation-dependent:
1. If all of the sheets (i.e., tabs) you want to group are next to each other with no other sheets in between, then select the first sheet using the tabs at the bottom, then select the last sheet while holding down the SHIFT key. You will now have a row of tabs that are whiter than the rest, indicating that they are grouped.
2. If the sheets you want to group are dispersed throughout, then select the first sheet you want to group, press the CTRL key, and then select each individual sheet you want to add to the grouping – while continuing to hold the CTRL key – until all of the sheets you want to group are white instead of gray.
You’ll notice in the image above that once you have some grouped sheets, the filename at the top of your workbook has changed from something like Workbook 1 to Workbook 1 [Group], alerting you that you have some grouped sheets in case you forget. Once the sheets are grouped, anything you do to the sheet you can see will also happen to every other sheet in the group. Be careful with this, as I’ve accidentally written over some data in sheets that I didn’t realize were grouped at the time. You’ll see after some practice, though, that it’s a great feature.
Sometimes you want to copy the exact formula from a cell and paste it into another cell on the same sheet, but the cell has some cell references that are not totally locked by having a “$” on both the number and letter components of each referenced cell (so you can’t just copy the cell and paste it elsewhere, because the formula – and hence the value – will change.) You look up to the formula bar and think, “I’ll just copy the text from the formula bar and paste it into the new cell.” However, when you select the formula from the formula bar and try to paste it into another cell, it doesn’t work because Excel thinks you’re trying to tell the formula to reference the new cell, and what used to be your pretty formula now just says something like “=B8” and the new cell just has a flashy border. So you hit ESC, and try to figure out another way to do it.
This tip isn’t really a trick, because all it requires is realizing that after you hit escape, the formula you copied is still on your clipboard. So the drill goes:
1. Copy the formula from the toolbar
2. Hit ESC
3. Move to the cell you want to paste the formula in
4. Select Paste.
If you are not sure about it, just try it!
This feature may not need to be used that often, but it might help you save some time once in a while. The “Transpose” option is in the Paste Special dialogue box that pops up any time you want to paste something in a special way (eg., you have most likely had the experience of pasting the values of a range of calculated formulas). You can right-click a cell to get to it, or you can choose Paste > Paste Special from the Edit menu. All the Transpose feature does is allow you to paste a list of items at a 90 degree angle to the direction you copied them from. If you copy a long column of data, Transpose will paste the data as a row starting in the cell you paste to. On the other hand, if you copy a row of data, Transpose will paste it as a list in the same column. If you copy an array of data, Transpose will anchor the array at its top left corner and flip it over. This is helpful if you have put a lot of work into a table and eventually realize it would look or work better if it was oriented differently. As an example, please consider the following data:
Example of Paste Special > Transpose
Suppose you have a table of existing values that you want to translate into new values. The VLOOKUP function allows you to lookup an old value and change it into a new value. More technically, the VLOOKUP function allows you to define an array (which is essentially a table of values) and return the value from a specified column of the array that is on the same row as the specified value in the first column of the array. This is frequently used in our FlexField software to map old segment values to new ones. The four arguments of the function are:
1. Lookup Value (this can be a value like “E”, but it can also be a referenced cell such as D13)
2. Array (in the form C6:F9, or more likely $C$6:$F$9 (obtained by hitting F4 once), since VLOOKUPs are very helpful when you need to translate a list of values into new values by looking up the old values in an array and matching them to their new, or mapped, values. Adding the “$”s locks the lookup array in place, which is exactly what you want. The array $C$6:$F$9 includes all the values in the table where C6 is the top left value and F9 is the bottom right value.)
3. Column Index Number (this is the number of columns to the right of the first column that you want the formula to look in)
4. Range Lookup (there are two possible values for this field – TRUE and FALSE. The majority of the time, you will want to use FALSE to get the function to work properly. FALSE will only return a value if an exact match for the Lookup Value is found in the first column of the array. TRUE will return a value, even if only an approximate match was found.)
The example below uses a mapping table to translate old account numbers into new account numbers. The mapping table is often located on a different sheet or even in a different file. The reason you cannot simply copy and paste the new account numbers from the mapping table into column B is because the order of the old account numbers are different in each. This displayed VLOOKUP formula looks up a value located in cell A4 (1234) in the D column between rows 4 and 32 – it finds 1234 in row 23 in column D. The VLOOKUP formula only looks for values in the first column of the array. The formula then returns the value on the same row (23), but in the third column of the array (column F), because you defined the third argument of the formula, Column Index Number, to be 3. You can see that cell B4 correctly displays 160005 in the image below.
VLOOKUP Example: The displayed formula is in cell B4.
In the formula for the first new account number, =VLOOKUP(A4,$D$4:$F$32,3,FALSE), notice that we have not applied “$”s to the first argument, A4, because as we copy the formula down to the remaining cells in the list, we want that argument to change and continue to reference the cell directly to the left of it. We have, however, used “$”s to lock the values of the second argument at $D$4:$F$32. This is the array (or table) that we are using to look up the new values, and we do not want it to change as we copy the formula to the remaining cells. We can see the power of VLOOKUP – it will search for a particular value in a table and return a corresponding value from a column in the table that you specify. You can then copy the formula to the remaining cells in the list, holding constant the array the formula is looking in but allowing the value being looked up to vary relative to the cell the formula is in.
The problem with VLOOKUP is that it only works if the column you want to return a value from exists to the right of the column that carries the Lookup Value, since the Lookup Value must be in the first column of the array. Sometimes, your spreadsheet may be set up in such a way that the column you want to return a value from is actually to the left of the Lookup Value column. In this case, it is necessary to use a combination of two functions: INDEX and MATCH. The functions do completely different things on their own, but when combined, they can perform a powerful “reverse VLOOKUP”.
First, let’s look at the functions individually.
1. =INDEX(Array, Row Number, Column Number)
As you can probably tell from the function arguments, you give Excel the array by drawing a box around it, then tell it to return the value that is at the intersection of the row and column that you define through the second and third arguments. It is not too complicated, but also not too useful when used on its own.
2. =MATCH(Lookup Value, Array, Match Type)
The Array argument in MATCH should be a one-dimensional array (read column) in this case, and Match Type – similar to VLOOKUPs Range Lookup argument – should be set to FALSE in order to find an exact match. What MATCH returns is the number of the row where it finds a match for the Lookup Value data. Now, one of the arguments for INDEX is Row Number. If we replace the Row Number argument with the MATCH function, we have just created our reverse VLOOKUP. The correct syntax is:
=INDEX(Array, MATCH(Lookup Value, Array, Match Type), Column Number)
Take a look at the example below. The value we are looking up, Employee ID, is to the right of the value we want to return, Employee Name, so we cannot use VLOOKUP. The formula we need to use is =INDEX($A$3:$D$9,MATCH(A14,$D$3:$D$9,FALSE),3). This formula takes the value in A14 (054) and finds it in column D. It then looks in the 3rd column of the array ($A$3:$D$9) and returns the value from the third column of the array (C) that is in the same row as 054 (Johnson, Debbie). Of course, in this case you could have just found the name yourself and copied and pasted it, but imagine if you had 3,000 employee IDs that you needed to match with a name. Lock the arrays with “$”s, but keep the lookup value unlocked so if you need to copy the formula to another thousand cells, they will continue to adapt and reference the correct cells.
Now, a little introduction to our FlexField software. FlexField is cutting edge software that allows a business to make rapid changes to its chart of accounts (i.e., the accounting flexfield) in Oracle E-Business Suite environments. One of the vital components of a FlexField project is the mapping component, during which the accounting or finance department of the business determines how the values of the current chart of accounts will be translated to the values of the new chart of accounts. This exercise is carried out in Excel for simplicity’s sake, and usually involves anywhere from 4 – 10 different spreadsheets. Because a business may have millions of rows of values in their current chart of accounts, each of the spreadsheets used for translating the values may contain millions of rows of data, making it cumbersome and difficult to deal with unless some shortcuts are employed.
After the business has decided how to map the values and has created a mapping table like the example on page 10, we use the VLOOKUP and INDEX/MATCH functions constantly to enable us to translate old values into new values and complete FlexField projects in a timely manner.
To access the Go To Special panel, just type CTRL+G and then choose “Special”. Most of the functionality here involves narrowing down data that has already been selected. For example, if you have selected an entire column that has blanks scattered through at random intervals, choosing to Go To “Blanks” will narrow your entire column selection down to a selection of only the blanks in that column. To give a FlexField mapping example of the importance of this function, we were working with a client who was mapping a set of over a million values to a new set of just under a million. For the most part, a mapping table that had been created by Finance and Accounting was used to translate the current values to the new values (which we used VLOOKUP to do). However, there were a handful (and with a million total rows, a handful was between four and five thousand) of values, scattered throughout the Excel file, whose mapping was not defined on the table. It was decided that all of the undefined current values were to be mapped to a single constant new value. What we were left with was thousands of blanks in a column, with no rhyme or reason to their positions, and we had to put the same constant value in every single empty cell. In order to work through this problem, we put the constant value in a nearby cell, copied the cell, selected the entire column containing the thousands of blank cells, selected Go To Special > Blanks, and selected Paste.
“Blanks” is not the only useful “special” Go To parameter. My favorites are:
1. Blanks
2. Visible cells only (You don’t need to do this when you have Autofilter on. Autofilter, by default, will only allow you to select visible cells. However, if you have hidden rows or columns, or used a Subtotal, it may be worth a look.)
3. Constants
4. Formulas
There are too many applications of Go To Special to go into here, but next time you are in a bind, just type CTRL+G and take a look at that panel.
