Other Spreadsheet Reporting
> Other Spreadsheet Report Styles Part 1 > Part 2
Other Spreadsheet Report Styles – Part 1
In last section, the reporting style of multiple grouped data on a fixed page layout is introduced. Let’s have a look here of some other commonly used reporting styles that are created on Excel spreadsheet easily and rapidly. The customer statement worksheet in the previous section is continued to be used as the report data here.
Simple Listing Report
Listing of data is the basic format of a report. In the same way of creating the template of a document printout, just put the cell captions enclosed with double colons on a spreadsheet to print the report. The cell captions of multiple value cells are put on the same row to create the data listing.
The list of data is printed onto the Excel spreadsheet:
All Excel formats and formulas that are on the same row of the multiple value cells will be brought forward with the data together.
You can put something, like “End of Report” or any text, in the location just under the row of multiple value cells. As the multiple values of data are rolled out on the spreadsheet, the layout of the table will be expanded with the rows of data, and all text and data under the multiple value data will be pushed forward.
..formatSeqFile
The purpose of this report directive ..formatSeqFile is to tell the program to list the data in a much faster way. You can place ..formatSeqFile anywhere on the spreadsheet.
The purpose of this report directive ..formatSeqFile is to tell the program to list the data in a much faster way. You can place ..formatSeqFile anywhere on the spreadsheet.
Repeating Page Header
You may like to print the same header, including report title and table header etc., across all pages of a report. Using the Page Layout feature of Excel, you can create a page header that will be printed across all pages of a report automatically.
To do so, go to Page Layout in the Excel spreadsheet and click Print Titles:
In Rows to repeat at top, add the range of rows as the page header:
Grouping and Subtotal using Excel Macros
You can execute Macro routines in Excel after report data are moved onto the spreadsheet by using the report directive ..Macro. One of the usages of running macros is to group your report data.
Create a Macro in Excel
Creating a macro in Excel is just easy, because you can use Record Macro feature to create the macro. In Excel, click View -> Macros -> Record Macro. Then add the name of the macro, in this example “SubTotal”, and start recording.
Creating a macro in Excel is just easy, because you can use Record Macro feature to create the macro. In Excel, click View -> Macros -> Record Macro. Then add the name of the macro, in this example “SubTotal”, and start recording.
Record the Macro for Subtotal
The first step is to select the first cell in the row of report data with multiple values . Excel will recognize the upper row as the table title automatically.
The first step is to select the first cell in the row of report data with multiple values . Excel will recognize the upper row as the table title automatically.
Then click Data -> Subtotal, and fill in the options with the field you want to group and the field you want to sum up or count in subtotal.
When recording is completed, go to View -> Macros and click Stop Recording to end the macro recording.
..Macro
After creating the macro for subtotal, the next step is to add dot dot command – ..Macro on the spreadsheet, so that the macro will be executed when the report is generated. Since the name of the macro in this example is “SubTotal”, ..MacroSubTotal is added onto the spreadsheet. This report directive can be placed in any location in the spreadsheet.
After creating the macro for subtotal, the next step is to add dot dot command – ..Macro on the spreadsheet, so that the macro will be executed when the report is generated. Since the name of the macro in this example is “SubTotal”, ..MacroSubTotal is added onto the spreadsheet. This report directive can be placed in any location in the spreadsheet.
The generated Excel report is as shown below. Definitely, you can discover much more usages about Excel macros combined with the spreadsheet report.
Hiding or Deleting Rows with Conditions
..HideRow, ..DeleteRow
You can choose to hide or delete some rows of data that you don’t want to show in the spreadsheet under certain conditions. To hide or delete a row, use the report directive ..HideRow or ..DeleteRow respectively. These two dot dot commands are used in parallel with the Excel IF() function, so that you can add the conditions where you would like to hide or delete the rows.
You can choose to hide or delete some rows of data that you don’t want to show in the spreadsheet under certain conditions. To hide or delete a row, use the report directive ..HideRow or ..DeleteRow respectively. These two dot dot commands are used in parallel with the Excel IF() function, so that you can add the conditions where you would like to hide or delete the rows.
In this example, let’s say we don’t want to show the customer “Geeee Heeee” in the spreadsheet, we add this formula in any cell of the row where the cell captions are defined:
=IF(Left(A4,5)=”Geeee”, “..DeleteRow”)
The formula will be brought forward with the expanded rows of data during printing. In the rows with the customer “Geeee Heeee”, the report directive ..DeleteRow will appear and tell the program to remove the rows accordingly.
The data of the customer “Geeee Heeee” are removed from the spreadsheet report.
