Beyond the rows and columns of a spreadsheet Excel offers the ability to create detailed reports. Reports can be formatted with borders, clip art, font formatting as well as number formatting. Visually a report is more appealing that the very ordinary view of the spreadsheet. Excel reports can be very basic or extremely complex. A basic report can be created rather quickly depending upon the amount of formatting. To create a basic report, it’s best to use a PivotTable. To learn about adding new rows and columns in Excel click at http://www.whiskydemalta.com/add-columns-rows-table-excel/
If using a workbook with data already entered the first step you should take is to organize and correct any data within the spreadsheet. Double-check that there is only one record in each row, one column per category, and one column per metric. For example, you will want to total all costs into one column versus separating costs out across several columns. Once the data is sufficiently organized create a report by following three simple steps.read more info on excel by clicking here
- Highlight all the columns and rows that you want included into your report. Click into a cell and holding down right mouse button move the mouse over all cells to highlight them.
- Select the INSERT tab at the top and click on the PivotTable button. A window will pop-up showing the selected table range. There’s also the option to Use an External Data Source. Next Choose whether you would like the PivotTable to be placed within an existing workbook or create a new workbook. Click OK.
- The PivotTable Field List will appear on the right that allows you to select the Rows, Columns, Filters, and Sum Totals for your report. Customize by dragging the fields from down into the areas
The Rows box will place the fields on the left side of the report. The Columns box will place the fields across the top of the report. The data you would like to summarize should be placed within the Values box. Each item you placed within the Values box can then be summarized by sum, count, average, minimum, maximum, or other calculations (In the screenshot below, I have chosen SUM)
That’s it. You now have a clear and informative report made from the raw data in your workbook. If you should choose to format your report you can choose colors by changing the Theme. Select Page Layout tab from the top, Click on Themes, and select one. Another option is to personally select the color scheme, fonts, and effects within the Themes tab. Within the Insert tab at the top you can also insert Charts, Headers, Footers, and a text box. To insert a chart based upon your report you can choose from the Recommended Charts, select any of the several options in charts, or choose the PivotChart.
Right clicking on the PivotTable will allow you to format the cells, change the number formatting, change the way the values are summarized, and other options. Another option is to choose the PivotTable Tools at the top.
There you can choose between ANALYZE or DESIGN (in Excel 2016) to make changes to your PivotTable. The ANALYZE tab allows you to change the name of the PivotTable, choose the Active Field, and adjust several settings. The DESIGN tab is for choosing what will be seen on the PivotTable for example whether the rows will be banded or whether Subtotals or Grand totals will appear on the Report.
To update or add new data to your PivotTable you need to make the changes in the original spreadsheet first and save. Open your workbook that has the PivotTable within it and right click anywhere in the PivotTable range. Select Refresh and the PivotTable should automatically show the updated or changed data. If you have multiple PivotTables that need to be updated select any cells in any of the PivotTables you are refreshing. At the top go to PivotTable Tools, select Analyze, click on Data and Click on the Arrow under the Refresh button and then click on Refresh All. To get further information about Pivot Table in Excel, click here
To delete a PivotTable that you no longer need or want, simply select the entire PivotTable by clicking in the top left of the PivotTable and either pressing DELETE on your keyboard. Deleting a single PivotTable will not change the data, charts associated with the data or any other PivotTables.