Excel Tricks – Data

Transpose data from a row to a column, or vice-versa

The best solution under the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box. Click OK, and Excel will transpose the column and row labels and data.

Convert calculations to literal values

This jewel, like #9, is also found under Edit | Paste Special. Experienced users may squawk that this tip doesn’t qualify as obscure. However, I decided to include it because, in teaching both beginning and advanced Excel classes to thousands of adult learners over the years, I’ve met too many people who never even heard of it–which is a crying shame.

Here’s how it works. Suppose you have a worksheet with columns and rows chock full of calculations, running the gamut from Sum functions to If tests to vertical and horizontal lookups. The calculations are correct and your data is pristine. You save the worksheet.

Now you need to use a subset of that worksheet in another worksheet. If all you’re going to do is print the subset of columns or rows, you can simply hide those rows and columns, print what you need, and unhide the columns and rows later to restore the sheet to its normal state.

But if you’re going to e-mail a copy of the spreadsheet to a coworker or a third party, you may not feel comfortable simply hiding certain rows and columns. You may want to delete them instead. The problem is, of course, if you simply start deleting rows and columns, you’re going to get error messages in the cells that depend on the cells you deleted.

The solution? First and foremost, save a copy of your pristine spreadsheet under a new name. Just go to File | Save As and add “_work” to the end of the “real” name. Use the Select All tip (#1) to select the entire sheet and then copy it. Without moving the cursor, go to Edit | Paste Special. Now, select the Values option, as shown in 
Figure S,and click OK. When you do, Excel will replace all the formulas with the values they’re currently calculating and displaying. At that point, you can delete columns or rows and move cells around without generating a single error message.

When Excel Chooses the Wrong Format for Your Data

  • •You want 00125678, not 125678
  • •The format of the cells that you are entering data in needs to be corrected.
  • •Go to Format and click on Cells. Excel displays the Format Cells dialog box.
  • •Click on the Number tab.  
  • •In the Category list, choose Text and click okay.

The gene name is Oct4 not 4-Oct!

  • •The previous fix for leading zeros will also work here.
  • •There is one caveat, you must change the format of the cells before entering the data.
  • •Otherwise 4-Oct becomes 37167

Deleting Empty Rows

To delete empty rows between data:

  1. Select all columns containing data.

2. Click the Sort icon (either Ascending or Descending).

Screenshot // Deleting Empty Rows

Deleting Empty Rows, excel tricks


• You have 5 columns of data you want to sort and Excel only allows you to sort 3.
• If you want to sort by columns A B C D E, select the whole spreadsheet, than sort by C D E, than A B.  This will result in all five columns being sorted.

Sorting IDs

You have a column of Ids that are F1, F2, ….F150 and would like to sort based on these ids.  How?
• The only way to make Excel sort the proper way is to change your ids to F001, F002, etc.
• =LEFT(C1,1) & RIGHT(“000” & RIGHT(C1,LEN(C1)-1),3)


Perform one-click data mining with AutoFilter

Go to Data | AutoFilter, and Excel will add drop-down arrows to the first cell in each column of data in your sheet. When you click on any of those drop-down arrows, Excel will display a list of the unique entries in that column. Just select the desired entry to limit the display of records
If you look closely, you’ll notice that the color of the drop-down arrow changes from black to blue whenever you make a selection. That change in color is a visual cue to remind you that your list has been filtered by a selection from that column.
As you probably guessed, you aren’t limited to filtering the list on just one column. You can click on the drop-down for two or more columns, and Excel will display only those records that match your selections in each column.
There are two ways to turn off AutoFilter. One is to click on each of the columns where you made an AutoFilter selection and choose the (All) option. The other way is to go to Data | Filter and select Show All.

Generate a unique list of entries in a column

You Want an Unique List of Values You have a column of data and would like only the unique values from it.
  • Select the column you want the uniqueness based on.
  • Choose Filter from the Data menu, and then choose Advanced Filter. A dialog box pops up.
  • I always like to choose the Copy to Another Location option.
  • In the Copy To Field, specify the cell where you want the list of unique, filtered values to be copied.
  • Make sure the Unique Records Only check box is xselected and click Okay.

Let Excel calculate your subtotals for you

The Subtotals feature is yet another life-changing tool for those who haven’t seen it before. In the old days, before the Subtotals feature was introduced, here’s how you generated subtotals: You’d sort your data, manually insert blank rows between the groups of data you wanted to subtotal, and manually insert the appropriate Sum functions. Many Excel users still take that approach when they want to generate subtotals, which is regrettable, since it provides many opportunities for errors.
Here’s a quick walk-through of how to use Excel’s Subtotals feature:
  • Save your worksheet under a work name. To do so, go to File | Save As and add “_work” to the original filename. Trust me, you don’t want to practice using the Subtotals function using the only “good” copy of your worksheet. As you’ll find when you sally forth into experimenting with this feature, a misstep can make a mess of your data that’s hard to clean up.
  • Sort your data on the column by which you want to subtotal. This is an important step, because the Subtotals feature doesn’t care if your data is sorted; it will simply subtotal records in the order they appear.
  • Click anywhere in your source data and go to Data | Subtotals. When you do, the Subtotal dialog box will appear and Excel will take its best guess as to the column on which you want to subtotal and the function you want to use (Sum) for those subtotals. (You can also generate subtotals using a host of other functions, such as Average, Min, and Max.)
  • Click OK to generate the subtotals.

Analyze selections with the AutoCalculate menu

Right-click in the vicinity of Ready in the bottom-left corner of Excel’s Status Bar to display the incalculably valuable AutoCalculate menu. Gone are the days when you manually key a few numbers off of a sheet to get a quick total. Now you can get it off the screen with a few clicks.


To create a PivotTable report:

1. Select any cell in the source data, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A).
2. Press Ctrl+F3, and then type the defined Name for the source data.
3. From the Data menu, select PivotTable and PivotChart Report.
4. In Step 1 of 3, select Microsoft Excel list or database, and then click Next.
In Step 2 of 3, in the Range box, press F3 to open the Paste Name dialog box, and paste the Name of the source data as defined in step 2.
5. Click Next.
6. In Step 3 of 3, click Layout (in Excel 97, go to step 8).
7. In Excel 2002 and Excel 2003, you can skip this step. Instead, click Finish in Step 2 of 3 and then create the PivotTable report by dragging the fields from the Pivot Table Field List dialog box to the PivotTable report.
8. In the Layout dialog box, drag the Data Fields to the white Data area, and drag all other fields to the white Page area (except fields that are not going to be used in the PivotTable report), and then click OK. 
9. In Step 3 of 3, click Finish. The PivotTable report is created.
10. Drag Data (in cell A5 in the screenshot) to the right of the PivotTable report to change the layout from horizontal to vertical.
11. The PivotTable report is now ready to be used. For more details on how to use the PivotTable report properly, see the other tips in this category.

PivotTable report – Adding a Calculated Field

To add a formula (Calculated Field) as a new column in a PivotTable report:
1. Select a cell in the PivotTable report.
2. Press Alt+P to select PivotTable dropdown icon from Pivot Table toolbar, select Formulas, and then Calculated Field.
3. In the Insert Calculated Field dialog box, type the formula name in the Name box.
4. In the Fields list box, select the first field name to insert and click Insert Field. The field name is copied into the Formula box.
5. Type / (in this example), repeat step 4 to insert the second field into the formula, and then click OK.
6. To format the new field, select a cell in the field and click the Field Settings icon on the PivotTable toolbar.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s