Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Expert Excel Help - TRANSPOSE to avoid re-keying data

This Microsoft Excel tip is a great time saver as it helps to avoid the need to re-key data when working with tables and charts.

Say you have been working with a table for some time and you decide you would like to swap the axis details over; the Transpose function allows you to do this.

Let's see how it works. The table above shows our sales league, however, we would like to highlight the year-to-date achievements of our team by placing the month down the side with Y-T-D totals at the bottom of an employee name column.

We first do this by selecting our table (it may be difficult to see here but the table is selected). We then choose the cell where we would like the top left corner of our new table to be, right click to see the options as shown in the example here:

From these options, click on Paste Special which brings up a dialogue box with a number of options. Select the All radio dial at the top and the Transpose check box at the bottom, then click OK:

Our transposed data will then appear.

With Transpose, Excel also moves all the formatting and our new table can end up looking a little messy. However, with a little work this is easily fixed.

For more help working with your data, contact out experts. Help with Transpose and other reference functions can also be found on the Microsoft Excel Help pages here.