Excel 2007’s Format As Table feature can make columns of data easy to read, while providing a set of tools that you can use to perform basic data analysis with a few mouse clicks. For example, say you have collected unit sales for 100 products sold during the last quarter. Your worksheet consists of three columns: Product Name in column A, Unit Price in column B, and Units Sold in column C. To convert this worksheet to a table, follow these steps:
1. Click in any cell within the data range.
2. Click the Home tab.
3. In the Styles group, click the drop-down arrow of the Format As Table command.
4. Point the cursor to the Table Style Medium 17 format. A preview of the format will appear.
5. Click the Table Style Medium 17 format. The format will apply to the table.
6. Click OK.
As you scroll through the table, note the locked column headers; with the column headers locked, they always appear on the screen. Follow these steps to add a column that will calculate the total sales for each product to the table:
1. Enter Total Sales in D1.
2. Enter =B2*C2 in D2.
Excel automatically calculates and copies the formula to the last product in the table.
You can now perform basic analysis on the Total Sales column. For example, to sort the total sales from highest to lowest, click the drop-down arrow in the Total Sales header cell and select Sort Highest To Lowest. You can also filter the table to show only the cells with above average sales or the top 10 or 20 performers.
When you complete your analysis, you can convert the table back to a data range by following these steps:
1. Click any cell in the table.
2. Click the Design tab under Table Tools in the title bar.
3. Click the Convert To Range command in the Tools group.
4. Click Yes at Do You Want To Convert The Table To A Normal Range?.
Miss a tip?
Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
