Excel Tricks – Data
Posted by aalberici
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:
- Select all columns containing data.
2. Click the Sort icon (either Ascending or Descending).
Screenshot // Deleting Empty Rows
Perform one-click data mining with AutoFilter
Generate a unique list of entries in a column
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
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
To create a PivotTable report:
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.
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
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.
Posted on April 28, 2016, in Uncategorized and tagged AutoCalculate, autofilter, calculations, cells, columns, coworker, data, data mining, empty rows, excel tricks, format, Menu, pivot, pivotTable, range, rows, sorting id, spreadsheet, subtotals, worksheet. Bookmark the permalink. Leave a comment.