5 tips for data manipulation in Excel

There's plenty you can do, if you know the correct formulas.

1 2 3 Page 3
Page 3 of 3

One way to deal with this is to add a new column with the team name for each player.

"The trick is that you need to have a pattern to follow," according to Webster. In the example above, the position column is empty for the team name rows but filled in for the player rows. By filling in just the first cell with the team name manually, you can then use this formula to automatically fill in the rest:

=IF(B3="",A3,C2)

That says: If cell B3 is blank, fill in the value of the cell in the first column of the same row (in this case A3). Otherwise, fill in the value from the cell that's just above it (in this case C2, which should be the team name from the row above for all the player rows). Make sure to start with the first player row after having manually entered the first header row.

See an example below.

You can add a new column to make it easier to sort a worksheet where column headers are interspersed with data.

Search and replace

Tip 5: Create a new column

You probably know that you can do a search and replace in Excel with a typical text-editor control-F find-and-replace. But did you know that you can also create an entirely new column in Excel based on search-and-replace on an existing column? That needs the =SUBSTITUTE function, using the syntax:

=SUBSTITUTE(CellWithText, "oldtext", "newtext")

For more of Webster's Excel tips, including how to do data lookups from another worksheet using VLOOKUP(), download her PDF document My Favorite (Excel) Things 2014 and the sample spreadsheet.

And for lots more on getting the most out of Excel:

This article, 5 tips for data manipulation in Excel, was originally published at Computerworld.com.

Copyright © 2014 IDG Communications, Inc.

1 2 3 Page 3
Page 3 of 3
7 inconvenient truths about the hybrid work trend
Shop Tech Products at Amazon