Stupid Excel data tricks

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:

	A	B	C
1	Jane	Doe	jdoe@domain.com

You want to end up with a standard name and address format:

Jane Doe <jdoe@domain.com>

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.

	A	B
1	"
2	Adding	quotes

The first formula relies on a platform-specific character number— 34 on Windows and 22 on the Mac:

=CONCATENATE(A2," ",CHAR(34),B2,CHAR(34))

Or you can reference a fixed cell containing a double quote:

=CONCATENATE(A2," ",$A$1,B2,$A$1)

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,">"))

generates

Jane Doe <Jdoe@Domain.Com>

while

=CONCATENATE(PROPER(A1)," ",PROPER(B1)," <",C1,">")

generates

Jane Doe <jdoe@domain.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

1. Go to the Toolbar Options (at the end of the bar you want to add it to)
2. Add or Remove Buttons
3. Customize
4. 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:

	A	B	C	D
1	Rate	0.485
2	Start	End	Miles	$
3	12,023	12,045	=B3-A3	=C3*$B$1

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.

Conditional formatting

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.

Click image to see full size

In the first rule, that's two double quotes after the equal sign.

For your projects, just:

1. Select the cells you want to color code
2. 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.

If statements

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
1	25	1000	=B1/A1	40
2	[blank]	2500	=B2/A2	#DIV/0!

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:

=IF(A2>0,B2/A2,"")

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:

=IF(A2>0,B2/A2,IF(A2<0,B2/A2,""))

Which works with any positive or negative number in A2.

1 Comment

Note: New comments disabled for a few days while debugging.