Skip to main content

5 Microsoft Excel 2010 tips and tricks for advanced users

Like learning a foreign language, trying to become a Microsoft Excel master comes with a "use or it lose it" quandary. You can learn all the tips and tricks in Excel you like, but if you don't practice them often enough, they slip from your mind, fleeing your memory without a trace. Pick up a new idea for Excel on Monday, and if you don't use it Tuesday or Wednesday, it'll be gone by Thursday.

The five tips in this article are things we think all advanced users of Microsoft Excel 2010 will not only learn quickly, but also use often enough to remember them tomorrow. They cover some of the most important features that spreadsheet makers should know about.

So read on, Excel experts… and while you're here, you might also want to take a look at our 10 handy tips for Microsoft Excel 2010, and our tricks for intermediate Excel users, too.

Insert a transposed copy of a table with two mouse clicks

How to transpose rows and columns, part I. Almost every Excel user will sometimes want to flip the rows and columns in a table. Excel offers two ways to accomplish this task – one simple, one complex. The simple method (described in this tip) is less useful, but it takes only a few keystrokes. Select a table that you want to transpose; move the cursor to a cell in a blank part of your worksheet.

On the Home tab, click the down arrow below the Paste icon, then move to the third icon under Paste Values. A tooltip tells you that the icon is labelled Transpose. Click on it, and a transposed version of the original chart gets pasted in. Unfortunately, the two charts aren't linked. If you change data in one chart, nothing gets changed in the other.

Transposition plus: Create a “live” transposed copy of a table

How to transpose rows and columns, part II. Let's say you want to see the same table in two parts of your worksheet, with one of them a transposed version of the other, and with any data that you change in the first version instantly changed in the other. This takes a bit of forethought, but it's easy when you get the idea. Select a table; count its rows and columns – for example, if the table is at A1:H11, then it has 11 rows and 8 columns. Select a blank region of your worksheet with the dimensions transposed – in this example, 8 rows and 11 columns. In the upper-left cell enter the formula =(TRANSPOSE(A1:H11), but with the addresses of your actual table. Press Ctrl-Shift-Enter, which is the little-known Excel keystroke that creates an array formula. Excel will put curly braces around the formula to indicate that it's an array formula. Any change you make in the original table will also appear in the transposed version, but you can't make changes directly to the transposed version.

Bonus tip: To remove the transposed table, you have to select the whole thing – and that can be tricky if you don't remember the exact dimensions of the array. The answer is to put the cursor in the array, press Ctrl-G to bring up the Go To dialog, click Special..., and click Current Array.

Create Sparkline microcharts in Excel

One of the smartest features that Excel 2010 introduced was "microcharts," or, to use Microsoft's branding for them, Sparklines. These tiny bar and line charts occupy a single cell, and represent a row or column of numeric data. You can experiment with these by opening a worksheet that has labels in the left column and numeric data in the remaining columns, inserting a new column between the labels and the data, and then creating a Sparkline (by clicking a cell in the new column, and selecting Insert, Sparklines, and either Line, Column, or Win/Loss). A dialog box opens that lets you enter the range of cells to the right of the selected cell either by typing in the address or selecting the cells in the worksheet itself. Click OK, and your microchart appears in the cell where you started. Use the Design tools on the Sparkline tab in the Ribbon to format the chart to your liking.

Add content or formatting to multiple sheets at once

You can add content or apply formatting to two or more of the sheets on a multi-sheet worksheet by "grouping" the sheets together. When you group multiple sheets, any content or formatting that you add to one sheet also gets added to all the other sheets, so you can add a row of headers to one sheet and have it automatically appear on all the sheets that are grouped together with it. To group all the sheets in a worksheet, right click on any of the tabs in the lower left of the window, and click Select All Sheets. If you only want to select two or more individual sheets, hold down the Ctrl key and click on the tabs of the sheets that you want to format or edit at the same time. When two or more sheets are grouped, Excel adds the word "[Group]" (in square brackets) after the sheet's name in the title bar.

Be careful when working with grouped sheets

Grouping is a powerful but dangerous feature. If you delete the contents of a cell in one grouped sheet, the contents of the cells at the same location in all the other grouped sheets will also be deleted. So before you start editing in a worksheet with grouped sheets, right click on one of the tabs at the lower-left and choose Ungroup Sheets.