Wednesday, July 13, 2011

How to Create a Report to Display Quarterly Sales in Excel 2007


1. Open a new Excel workbook. A blank spreadsheet will appear on the page.
2. Click on cell 'A1,' which is the top-left cell in the spreadsheet. Type 'Quarter' into the cell and press 'Enter.' Excel will automatically select cell 'A2,' as it is directly beneath the first cell.
3. Type the names of the quarters into the cells directly beneath the first one. Each quarter should have its own cell, and be sure to include year numbers of your data spans more than four quarters. After you type the name of each quarter, press 'Enter' to go to the next cell.
4. Select cell 'B1,' which is directly to the right of the 'Quarter' cell. Type in your data's header here. If you only have one set of sales numbers for each quarter, you can simply type 'Sales' into this cell. If your data is broken down by different regions, stores, channels, or in any other way, enter in a header for the first data series into this cell, then enter the next header into cell 'C1,' and continue until each series of data has a header.
5. Enter your numerical sales data into the cells where the rows representing the quarter intersect the columns representing the data fields.
6. Click cell 'A1.' Press 'Ctrl' and 'A' to select the entire table. Click the 'Insert' tab at the top of the screen. Select the 'Column' button, and choose one of the 'Clustered' column chart options. Excel will create a chart based on your table data. The column headers will automatically be listed to the right of the chart, and the quarters will be listed beneath it.
7. Select the first empty cell to the right of your column headers. Type 'Total' into this cell. Select the cell just beneath this one and enter this formula: '=sum(B2:XY)' where 'XY' is the column letter and row number of the cell just to the left of the current one. Press 'Enter' to complete the formula. Select the cell and move your mouse over the bottom-right corner. Click and hold the mouse button while you drag the mouse down to the last row in the data field. Release the button to copy the formula all the way down the table, making it easy to read the total sales for each quarter.

Blogger news