Do you ever have to distribute reports as Excel files? Sometimes PDF just doesn’t cut it, especially if a file is meant to be interactive. But if we just save the file and e-mail it without considering the readability for the recipients, our beautiful spreadsheet can so easily miss the mark. If you have not optimized the spreadsheet as a report to be viewed it can give the recipient poor user experience.
Here are some of the common pitfalls
- Page break preview is turned on, giving ugly blue lines all over your workbook
- The worksheet was saved with the scroll position too far down the worksheet
- The workings section of the spreadsheet is displayed, which makes your report look nothing like a report and more like . . . well . . . workings!
- The grid lines, column letters and row numbers are displayed, which isn’t required if it is a report.
- The first page the recipients sees is the page you saved the file on, not the first page of the report.
Oh dear, you’re the lovely report, now looks a bit rubbish. It can be very time consuming to go through each worksheet and optimize it for presentation. So, why not create a macro which can do that for you. It will make sure that your reports look as good as they can.
The VBA Code
This macro will loop through every worksheet and ‘optimize’ it for presentation.
Sub OptimizeReportView() 'Declare the worksheet variable Dim Ws As Worksheet 'Loop through each worksheet to prepare it for distribution For Each Ws In Worksheets Ws.Activate 'Close all of groups Ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'Set the view settings to normal - looks better than print preview ActiveWindow.View = xlNormalView 'Remove the gridlines ActiveWindow.DisplayGridlines = False 'Remove the headings on each of the worksheets ActiveWindow.DisplayHeadings = False 'Get worksheet to display top left Ws.Cells(1, 1).Select Next Ws 'Find the first visible worksheet and select it For Each Ws In Worksheets If Ws.Visible = xlSheetVisible Then 'Select the first visible worksheet Ws.Select 'Once the first visible worksheet is found exit the sub Exit For End If Next Ws End Sub
Adapting the code for your requirements
The code above is what I use, but it may not be ideal for your circumstances, maybe you need to include some other options:
- Protect workbooks
- Protect worksheets
- Hide worksheet tabs
There are so many options, so if you’ve got other suggestions then leave a comment. If you’re not sure of the VBA code for any of these small changes you could use the Macro Recorder and simply copy and adapt the code into the example above.