Stupid Excel data tricks
These days, most of us do more in Excel than the VisiCalc creators would have dreamed. Following are a few of my favorite tricks for working with data in Excel (the raw side, not the chart side). This article includes:
- Combining strings of text with Concatenate
Updated to include working with double quotes
- Changing the CASE of text
- Pasting values instead of formulas
- Adding toolbar buttons
- Swapping rows and columns with Transpose
- Quick adding and removing rows and columns
- Keeping cell references constant
- Conditional formatting
- If statements
Combining strings of text with Concatenate
This is a great little function, useful for combining bits of information into a single value. Say you have the following data:
You want to end up with a standard name and address format:
Jane Doe <firstname.lastname@example.org>
Your formula would be:
=CONCATENATE(A1," ",B1," <",C1,">")
Individual pieces are separated by commas, with any new text you're adding in double quotes—here spaces and angle brackets.
Update: Some of you appear to be searching for a solution that adds double quotes as part of the string, such as combining the two cells below into Adding "quotes". I've found two inelegant approaches.
The first formula relies on a platform-specific character number— 34 on Windows and 22 on the Mac:
Or you can reference a fixed cell containing a double quote:
Changing the CASE of text
Ever receive a name list in ALL CAPS? This function is a fast way to change the format. Proper can be combined with Concatenate—imagine our original example started with JANE DOE in cells A1 and B1. You have two options for the nesting:
=PROPER(CONCATENATE(A1," ",B1," <",C1,">"))
Jane Doe <Jdoe@Domain.Com>
=CONCATENATE(PROPER(A1)," ",PROPER(B1)," <",C1,">")
Jane Doe <email@example.com>
UPPER and LOWER work in the same manner. If the list is of modest size I'll take a visual scan to check for special name formats like ones with a "de", but if it's 20,000 names I'm satisfied with knowing the list is in a better format than it started.
Pasting values instead of formulas
I use Paste Special Values both to bring information into Excel without formats, but also to convert cells which contain formulas into simple data (such as from Concatenate or Proper). You probably know you can access this through the Edit menu, but did you know there's a toolbar button you can add?
Follow the steps below to add buttons—Paste buttons are in the Edit category.
Adding toolbar buttons
- Go to the Toolbar Options (at the end of the bar you want to add it to)
- Add or Remove Buttons
- Select the category you need (sometimes similar functions appear in a couple groups) and then drag the button up to the toolbar
Swapping rows and columns with Transpose
At the bottom of the Paste Special dialog is an option to "Transpose." When you use this, row 1 becomes column A, row 2 column B, etc. There's no direct hotkey for Transpose, but you can add a Paste Special button to the toolbar the same way I describe for Paste Values.
Quick adding and removing rows and columns
Hands down, the four buttons I use most on my toolbar are:
These insert a row, insert a column, delete a row, and delete a column. When you have a row or column copied to the clipboard and use the insert buttons, Excel will paste as a new row/column instead of over the selected row/column.
You can add these to the toolbar just like how I describe for Paste Values. The two insert buttons are in the Insert category, and the deletes in the Edit category.
Keeping cell references constant
Imagine an expense tracking spreadsheet. For your mileage, the value is the same for a given expense report, but does change every year. So, set up your spreadsheet something like this:
You can now copy the formula in D3 for all subsequent rows, with it always referring back to B1. If you ever cut and paste the value in B1 to another cell, the reference will update to the new location.
While typing a formula, you can fix the location with the F4 key. Sometimes you only need to fix the column relative to your formula, in which case you'd use $B1, or to fix the row B$1.
This is great for data cleaning and for any time you're using Excel for project tracking. What conditional formatting does is change a cell's background and/or font when its contents matches your criteria. For projects, I'll often have a series of milestones where I'm tracking completion, and I'll color code the cells with "p" as partial, "c" as complete, or "na". For data, I'll sometimes be looking for exceptions, such as this capture here which highlights empty cells as red, and cells 1,000 or higher as green.
In the first rule, that's two double quotes after the equal sign.
For your projects, just:
- Select the cells you want to color code
- Format menu, Conditional Formatting
Note: If you have mixed numbers and text data, a rule of Greater or equal to 0 will highlight the text (such as your column or row headings). However, if you set the rule to Between 0 and a million or so, it will just highlight numbers.
I find If statements hugely useful, both to transfer data between cells and also to deal with missing data points. For example, if you have the following formulas:
|A||B||C||Result for C|
I find the #DIV/0! error distracting in spreadsheets, because often the blank or 0 cell is allowed in my data set. So instead I can make the formula a little smarter with an IF:
This translates to: If A2 is greater than 0, Then use the formula B2/A2, Else make the cell empty (two double quotes in a row). When you can't get the result you want with a single If, you can start nesting them in the Else clause, such as:
Which works with any positive or negative number in A2.
Note: New comments disabled for a few days while debugging.
Need a Hand?
A little help can add a lot of polish—or just save hours and headaches:
I rely on Query Group for prompt, accurate turnaround of my reporting projects. Being able to call on Ann lets me take on projects when my full time staff is already busy.
Using CONCATENATE with double-quotes simply requires escaping the quote. That is done as follows:
This would result in the string: "in quotes!"
Notice that double-quote itself is the escape character (similar to backslash in C++/C#".