5 tips for data manipulation in Excel

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

1 2 3 Page 2
Page 2 of 3

Reshaping data

Tip 3: Create multiple rows out of only one

Sometimes you need data in a format with one row for each observation, but what you already have comes with multiple observations for each row instead. In Webster's example of Affordable Care Act Exchange plan pricing, there is a column for prices in each age group: 1-20 years old, 21 years old, 22 years old and so on. However, some visualization and analysis tools require one row for each plan/price combination, not one row with multiple prices.

Tableau visualization software is one such tool that needs one data point per row, not multiple data points per row, so the vendor created a Tableau Reshaper Tool that works with recent versions of Excel on Windows.

You can download this free tool from the Tableau website. Although one add-in says it's for Excel 2010, it worked fine with Excel 2013 on my Windows 8 PC.

Several CAR attendees said they've spent hours reshaping large data sets by manually cutting and pasting, and the free Tableau tool will save them a lot of time. You don't need to have other Tableau software installed on your system to use it.

The columns you're keeping as row ID columns should be placed on the left, and all your data columns on the right. To use the reshaper tool, put your cursor on the first cell with data that you want transformed. Then go to the Tableau menu and choose reshape data. Say OK. You can watch a brief example below.

To format one row per observation, you can use a free tool called Tableau Reshaper with recent versions of Excel on Windows.

Tip 4: Create more easily sortable data

Another common data format problem is when you get a "spreadsheet" that's less like a sortable table of data and more like a Word document with column headers. One example: a spreadsheet with the name of a team on one row followed by all the players on that team, then the name of another team right below followed by the players and so on. It's difficult to analyze a worksheet where column headers are interspersed with data, since you can't easily sort, filter or visualize data by team.

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