build apps by user

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.
use this customer statement worksheet as the source of report data.

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 spreadsheet template for the reporting style of sequential list of report data
The list of data is printed onto the Excel spreadsheet:
print sequential list of report data on 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.
sequential report style with formula and formatting.
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.
add some text at the end of report.
add layout of table to the report.
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.
add the report directive ..formatSeqFile to speed up report printing.

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:
click page layout and print titles in Excel
In Rows to repeat at top, add the range of rows as the page header:
In rows-to-repeat-at-top of Excel, add the range of cells for the 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.
you can use macro together to generate the excel report
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.
start recording a macro in Excel spreadsheet
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.
select the first cell captions in the spreadsheet template
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.
click subtotal an select options
When recording is completed, go to View -> Macros and click Stop Recording to end the macro recording.
click stop recording of macro in excel 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.
add report directive to run the macro
The generated Excel report is as shown below. Definitely, you can discover much more usages about Excel macros combined with the spreadsheet report.
the final generated Excel 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.
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”)
add Excel formula - if function to add ..DeleteRow conditionally
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 formula is brought forward to the multiple rows of data.
The data of the customer “Geeee Heeee” are removed from the spreadsheet report.
the customer is removed from the Excel report.