Hui’s Excel Report Printer

Share

Facebook
Twitter
LinkedIn

Over a decade ago I was working on a very large and complex budget model, come to think of it I still am?

It involved 4 linked Excel workbooks, about 30 worksheets, all different, and multiple views of each worksheet.

There were regular Worksheets and Chart Sheets interspersed throughout.

Some of the Ranges had Outlined/Grouped Totals that were indented on some reports, but not on others depending on whom the various reports were going to.

It was a great budget model until you had to print a copy of it.

And of course the different levels of Managers all want different reports etc, etc.

 

The Solution

To solve this I developed a simple VBA routine which has evolved over the years to what is presented here.

The basic idea is to add a Printing Control sheet to your workbook.

This sheet has a list of print views, not Excel views, of various pages within the current workbook.

Each page can be setup as you wish and allows for a number of common parameters for each printed page.

Pages can be listed, multiple times if required, with different ranges or outlining selected each time

The Code handles Worksheets and Chartsheets, Normal and Named Ranges, Page Orientation, Page Size, Page Grouping and Headers/Footers.

As a user you setup the sheets as a list in the order you want them, with appropriate parameters.

The code then:

  • Loops through the list,
  • Obtain the parameters,
  • Sets up the print page and
  • Prints it.

You just need to sit back and wait for the printer to jam.

HOW DO I USE IT

Download the sample file here Excel 97-03, Excel 2007/10

You can use the sample file as is, for demo purposes or read on later where I describe how to use this in your workbooks.

Open the workbook and Goto the “Print_Control” worksheet.

Browse through the various Headings in Row 4 and field values below them.

Note that some of the Row 4 cells have comments in which explain what options are available.

Each field is described below:

No.

The Row No. in the list of page layouts available.

This has no use except when someone says the 5th page should be…

Description/Header

A text field that is used as a Reminder of the layout of the Page Setup also serves as a Centred Header.

Status

Print = On

Don’t Print = Off

The code only prints the pages marked as On.

Sheet

The name of the Worksheet or Chartsheet you want to print

Area

The Range on the Sheet that you want printed

Ignored for Chartsheets.

Land/Port

Specify if the page should be printed Landscape or Portrait

Ignored for Chartsheets.

Chartsheets are printed in Landscape.

Pages Wide

How many pages wide should the Range be printed on

This is fixed at 1 for Chartsheets.

Pages Tall

How many pages tall should the Range be printed out on

This is fixed at 1 for Chartsheets.

Copies

How Many Copies do you want of that individual page.

Rows & Columns

If outline/grouping is used specify what level of Indentation should be used for the Rows and Columns.

0 – Leave as is

1 – Indent 1 level

8  – Indent 8 levels

The maximum indentation is 8

Ignored for Chartsheets.

Footer (Left)

A description field printed as lower left footer.

No. of Copies

This specifies the Number of Copies of the Whole Report you want

Print All “On” Areas

The Print All “On” Areas Button executes the code and prints out a number of copies of the report as specified in the various page setups.

The printing is done on the default printer on your PC,

Important: Ensure that the printer you want to use for the job is set as the default before you start Excel.

You can print to a PDF file by specifying your Adobe or other PDF Printer as the Default Printer.

I’m sorry, This doesn’t fix the printing multiple pages to multiple files when printing to PDF issue.

 

Warning ! I maybe old school but I still recommend saving before printing !

 

HELP

There is limited help built into the system, That’s what this Post is doing.

Some of the field headings have comments which show what values are acceptable in those fields.

HOW DO I ADD THIS TO MY WORKBOOK ?

To add this to your workbook, copy the Print_Control worksheet to your workbook

  1. Open your workbook.
  2. Open the Demo File
  3. Copy the Print_Control worksheet by Right Clicking on the Print_Control tab, and copy to your workbook.
  4. Run the VBA Code using the “Setup Print Control Named Formula” Button

That’s it.

All the code required for the printing is part of the Print_Control page.

 

HOW DOES THE VBA WORK ?

The following describes the VBA Code driving this worksheet.

To examine this goto VBA (Alt F11)

Select the workbook and double click on Sheet0 (Print_Control)

The code should appear in the right hand window

If you are unfamiliar with VBA it may be worth going through Chandoo’s Crash Course in VBA

There are 2 Subroutines and a Function in this system which are documented below

 

Print_Reports

This is the main subroutine that drives the printing

It is called by the Print All On Button and when finished returns the user to the Print_Control worksheet.

All the VBA code is in RED,

Comments and notes are in BLACK before the line or section they refer to.

= = = = = = = = = = = = = = = = = = =

At the start of the Print_Reports subroutine, setup variables for later use

Option Explicit

Public Sub Print_Reports()

Dim PrintArea As Variant

Dim i As Integer

Dim j As Integer

Dim sht As Long

Dim Orientation As String

Dim NCopies As Integer

Dim PWide As Integer

Dim PTall As Integer

Dim Footer As String

Dim Header As String

Dim Sheets As String

Dim gRow As Integer

Dim gCol As Integer

Dim PaperSize As String

Dim msg As String

Dim tmp As String

Turn off the Automatic Calculation so that it is faster and isn’t as jerky

Application.Calculation = xlCalculationManual

This loads the entire array of the Print_Control page into an array called PrintArea

PrintArea = Worksheets(“Print_Control”).Range(“Print_Control”).Value

This sets up a loop for the No of Total Copies of the Whole report

For j = 1 To [Copies].Value ‘Loop through the No of Copies

This sets up a loop for the to check each line of the Print Control area

For i = 1 To UBound(PrintArea, 1) ‘Loop through the print area

If the Column Status is On print using that line of settings

If UCase(PrintArea(i, 3)) = “ON” Then ‘When On is enabled Print using the settings

Extract the settings from the stored array, row i

Header = PrintArea(i, 2) ‘Set Header variable

Orientation = PrintArea(i, 6) ‘Set Orientation variable

PWide = PrintArea(i, 8 ) ‘Set Pages Wide variable

PTall = PrintArea(i, 9) ‘Set Pages Tall variable

NCopies = PrintArea(i, 10) ‘Set No Copies variable

gRow = PrintArea(i, 11) ‘Set Row Group Expansion

gCol = PrintArea(i, 12) ‘Set Column Group Expansion

Footer = PrintArea(i, 13) ‘Set Footer variable

Check paper sizes against the built in page sizes

If PrintArea(i, 7) = “A4” Then

PaperSize = 9

ElseIf PrintArea(i, 7) = “A3” Then

PaperSize = 8

ElseIf PrintArea(i, 7) = “A5” Then

PaperSize = 11

ElseIf PrintArea(i, 7) = “Legal” Then

PaperSize = 5

ElseIf PrintArea(i, 7) = “Letter” Then

PaperSize = 1

ElseIf PrintArea(i, 7) = “Quarto” Then

PaperSize = 15

ElseIf PrintArea(i, 7) = “Executive” Then

PaperSize = 7

ElseIf PrintArea(i, 7) = “B4” Then

PaperSize = 12

ElseIf PrintArea(i, 7) = “B5” Then

PaperSize = 13

ElseIf PrintArea(i, 7) = “10×14” Then

PaperSize = 16

ElseIf PrintArea(i, 7) = “11×17” Then

PaperSize = 17

ElseIf PrintArea(i, 7) = “Csheet” Then

PaperSize = 24

ElseIf PrintArea(i, 7) = “Dsheet” Then

PaperSize = 25

Else

PaperSize = 9 ‘Defaults to A4

End If

Activate the relevant sheet

This checks that the sheet exists first

tmp = PrintArea(i, 4)

SheetExists(tmp) is a UDF that’s checks if the sheet exists and returns True or False

If Not SheetExists(tmp) Then

msg = “Sheet ‘” + PrintArea(i, 4) + “‘ not found.” + vbCrLf + “Check the sheets Name.”

msg = msg + vbCrLf + vbCrLf + “Processing will continue for remaining sheets.”

tmp = MsgBox(msg, vbExclamation, “Sheet not Found”)

Else

The sheet exists now process

Select the sheet

Application.Sheets(PrintArea(i, 4)).Select

Check if it is a Worksheet or a Chartsheet

If ActiveSheet.Type = -4167 Then ‘Its a worksheet

Turn off screen updating

Application.ScreenUpdating = False

Select the relevnt area of the sheet

ActiveSheet.PageSetup.PrintArea = PrintArea(i, 5) ‘Select the relevent Print Area of the Sheet

Set Outline levels

ActiveSheet.Outline.ShowLevels RowLevels:=gRow, ColumnLevels:=gCol ‘Set Outline Grouping

Apply print settings

With ActiveSheet.PageSetup ‘Set print settings

.PrintTitleRows = “”

.PrintTitleColumns = “”

.LeftHeader = “”

.CenterHeader = Header ‘User Defined Header (Shift to Left or Right as required)

.RightHeader = “”

.LeftFooter = Footer ‘User Defined Footer (Shift to Left or Right as required)

.CenterFooter = “”

.RightFooter = “”

.LeftMargin = Application.InchesToPoints(0.1)

.RightMargin = Application.InchesToPoints(0.1)

.TopMargin = Application.InchesToPoints(1.0)

.BottomMargin = Application.InchesToPoints(0.4)

.HeaderMargin = Application.InchesToPoints(0.1)

.FooterMargin = Application.InchesToPoints(0.3)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.CenterHorizontally = False

.CenterVertically = False

.Draft = False

.PaperSize = PaperSize ‘ User Defined Paper Size

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = False

.FitToPagesWide = PWide ‘User Defined No Pages Wide

.FitToPagesTall = PTall ‘User Defined No Pages Tall

.PrintErrors = xlPrintErrorsDisplayed

End With

Apply page orientation settings

If Orientation = “L” Then ‘User Defined Page Orientation

ActiveSheet.PageSetup.Orientation = xlLandscape

Else

ActiveSheet.PageSetup.Orientation = xlPortrait

End If

Turn Screen updating back on

Application.ScreenUpdating = True

Finished setting up Worksheet goto the Printing area

Else ‘Its a Chart page

Turn Screen updating off

Application.ScreenUpdating = False

Apply print settings

With ActiveChart.PageSetup

.LeftHeader = “”

.CenterHeader = Header

.RightHeader = “”

.LeftFooter = Footer

.CenterFooter = “”

.RightFooter = “”

.LeftMargin = Application.InchesToPoints(0.1)

.RightMargin = Application.InchesToPoints(0.1)

.TopMargin = Application.InchesToPoints(1#)

.BottomMargin = Application.InchesToPoints(0.4)

.HeaderMargin = Application.InchesToPoints(0.1)

.FooterMargin = Application.InchesToPoints(0.3)

.ChartSize = xlScreenSize

.PrintQuality = 600Change to 300 for Excel 97-03

.CenterHorizontally = True

.CenterVertically = True

.Orientation = xlLandscape

.Draft = False

.OddAndEvenPagesHeaderFooter = False ‘Removed from 97/03 Ver

.DifferentFirstPageHeaderFooter = False ‘Removed from 97/03 Ver

.EvenPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.RightHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver

.EvenPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver

.EvenPage.RightFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.RightHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.RightFooter.Text = “” ‘Removed from 97/03 Ver

.PaperSize = PaperSize

.FirstPageNumber = xlAutomatic

.BlackAndWhite = False

.Zoom = 100

End With

Turn Screen Updating back on

Application.ScreenUpdating = True

End If

Now Print the active sheet using user defined No. Copies

ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True

End If

End If

Next i

Next j

Clear PrintArea array, just in case

PrintArea = Null

Turn Auto Calculation back on

Application.Calculation = xlCalculationAutomatic

Go back to the Print Control sheet

Application.Sheets(“Print_Control”).Select

End Sub

= = = = = = = = = = = = = = = = = = =

The SheetExists Function

This is a Function that is used by the Print_Reports subroutine to check if a sheet exists.

= = = = = = = = = = = = = = = = = = =

Function SheetExists(SheetName As String) As Boolean

‘ This function Returns TRUE if the sheet exists in the active workbook

SheetExists = False ‘Set default value of SheetExists

On Error GoTo NoSuchSheet ‘Set error trapping such that if the sheet doesn’t exist it will exit

Check length of sheet name, if the sheet exists it will return a value, otherwise an error

If Len(Sheets(SheetName).Name) > 0 Then

The sheet exists so set SheetExists = True and exit

SheetExists = True

Exit Function

End If

NoSuchSheet:

The sheet doesn’t exists so use default SheetExists = False and exit

End Function

= = = = = = = = = = = = = = = = = = =

The Setup_Print_Control_Named_Formula Subroutine

This is a simple subroutine that sets up the 2 named formula for use the first time a sheet is used.

= = = = = = = = = = = = = = = = = = =

Sub Setup_Print_Control_Named_Formula()

Setup Named Formula “Print_Control” which is the table of settings

ActiveWorkbook.Names.Add Name:=”Print_Control”, RefersToR1C1:= _

“=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))”

ActiveWorkbook.Names(“Print_Control”).Comment = _

“Used by the Print_Reports Subroutine”

Setup Named Formula “Copies” which is the No of Copies of the Whole Report

ActiveWorkbook.Names.Add Name:=”Copies”, RefersToR1C1:= _

“=Print_Control!R26C13”

ActiveWorkbook.Names(“Copies”).Comment = “Specifies the No. of Copies for the Print_Reports Subroutine”

End Sub

= = = = = = = = = = = = = = = = = = =

NAMED FORMULA

The code relies on two Named Formulas

Copies:

=Print_Control!$L$27

Print_Control:

=OFFSET(Print_Control!$B$4,1,,COUNTA(Print_Control!$B$5:$B$24),COUNTA(Print_Control!$4:$4))

Automatically adjusts the Print_Control Named Formula for the number of Page Setup lines and Fields to be processed

If you have queries about how any of the above code works, please let me know in the comments below:

 

WHAT DOES THE ARRAY “PrintArea” DO ?

The print area array stores the values of the Print_Control range in a 2 dimensional array which represents the Print_Control range.

This is done for a few reasons, but simply it is faster as it results in less reading of the worksheet

It also allows more flexibility in the subsequent processing as all the data is in one area.

 

DOWNLOADS

Download the sample file here Excel 97-03, Excel 2007/10

 

WHAT’S NEXT

There are a number of parameters used in the Print Setup area which are not used or not used in the 97/03 version.

The code above is easily extended to include these if you desire.

One day when I have a spare moment (Most likely in 2025!) I will add the option for automatic incremental Page Numbers.

 

CLOSING

This code has saved, my staff and I, hundreds and hundreds of hours over the past decade whilst printing complex Excel workbooks.

This functionality was also one of the more requested issues from our poll of 3 months ago We Want Your Ideas!

I hope you enjoy it as much as I have ?

 

Updates

I will be extending the functionality of this in the future and so if you have any suggestions, lets hear them in the comments below:

 

How have you tackled large print jobs ?

I look forward to your comments below:

 

Hui…

For a list of my other contributions at Chandoo.org please visit; Hui.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

46 Responses to “Big trouble in little spreadsheet”

  1. Duncan says:

    I have faced this self same problem with my own delegates recently ... TWICE with two separate delegates in different places.

    Take a look at my own blog post on one of them where I describe my pivot table solution: http://excelmaster.co under the heading of you need pivots

    Duncan

  2. Duncan says:

    I have faced this self same problem with my own delegates recently ... TWICE with two separate delegates in different places.

    Take a look at my own blog post on one of them where I describe my pivot table solution: excelmaster dot co under the heading of "you need pivots"

    Duncan

  3. Mary says:

    Really interesting thanks, I always thought it was due to my very complicated sheets and a slow computer (AMD + 4GB ram). But after thinking a little, I removed a whole pile of formatting in my tables, and now things are running a little faster!

  4. Kenneth says:

    I do leave my spreadsheets in manual calc mode, but that's because I constantly work with spreadsheets that contain between 20,000 - 100,000 rows, sometimes up to 600,000, with generally around 20-30 columns (before any calculated fields are added). Maybe I'm an exception, due to the amount of data I work with, but I'll throw this out there and see if I'm missing opportunities for efficiency.

    I have a workbook that analyzes accounts to determine call priority (to collect past due AR), factoring in their ATB (aged trial balance, how much they owe, how much is current, 1-30 days past due, 31-60 days past due, etc), ADP (average days to pay), percent of credit limit used, whether they've had an NSF (insufficient funds notice, basically their check bounced), if we have unapplied cash on their account (like an unused credit that could balance out a due amount if we call and get their approval to use it for that purpose), their pay terms (how long they have from invoice until payment is due), and factors in all those bits of data to come up with a weighted point total to see which accounts need to be at the top of the call list, which is then distributed amongst the collectors.

    There's generally around ~20,000 accounts on the list. The data comes from 5 different SAP reports. I originally had a different workbook for each report and linked to them from the ranking/calc spreadsheet, but I sped it up a bit by putting all the data on tabs (in calculation order) in one workbook from left to right leading up to the calculation sheet at the end.

    The things I've found so far that help cut down calculation time:

    Clean and sort the raw data before putting it into the calculation workbook. This may seem obvious or blasphemous based on your background. I used to work in digital illustration and it was key to always have the original, untouched raw data locked in at the back with all the adjustments layered above it so you didn't ever lose any of the source, so my initial practice was to always build my sheets to use the raw data exactly as it comes out of SAP. However, the calculation time saved when you do just a little work to make sure the data is arranged in an orderly fashion is significant. Every time you touch the data there's another opportunity for error, though, so it's something to be balanced.

    When you need to remove certain rows from a table based on criteria, sort the table by that criteria first and it drastically reduces the time needed when you delete all rows with matching criteria.

    If you have multiple columns that reference the same source table, don't use index & match in every one of those columns. Use match in one column to find which row of the source table to look at, then just use index for the other columns and reference the row number returned from the match column. For example, the final piece of my workbook is a sheet that builds a worklist for an individual collector. You put in the collector number at the top and it compares that to the total number of collectors and builds the list. If you have 7 collectors, then this looks at the ranking and lists every 7th account from top to bottom. One column generates the row numbers of those accounts using match, then the rest use index to pull the data over.

    I'm about to switch worlds again, this time to accounting, so I'm doing a bit of housecleaning to make sure all my SOPs are in order and the spreadsheets I use are as clean and efficient as possible. If anyone sees a flaw in what I've described, or an opportunity to speed it up (preferably without using VBA, I'm leaving the work to people that will need a lot of coaching just to understand index and match, so VBA is only going to be a deeper mystery to them) I'd be happy to hear it.

    • Hui... says:

      @Kenneth

      I would also consider using of Power Pivot as I believe that can combine data from varying sources

      • Kenneth says:

        Hui...

        Thank you for the suggestion. Unfortunately it isn't viable in this case. I approached that angle about a year ago, but was shot down. We're still on Excel 2010 and installing the powerpivot addon requires a lot of hoops to be jumped through with IT to get someone to install it (our machines are locked down pretty tight). Since I built this monstrosity of a spreadsheet to simulate what an upcoming SAP module is going to do automatically, my request was shot down. The problem is that the upcoming SAP module, which is nearly finished, hasn't lived up to expectation and will provide only a fraction of the functionality my spreadsheet does. Fun world.

        • juanito says:

          Kenneth -
          Your situation resonates! IT really is digging itself a hole in many large organizations, and that has a lot to do with why we have a boom in "self-service" (or, "leave me alone!") user-led solutions.
          Just one question: are you quite sure you aren't able to install the PowerPivot add-in? It's really very simple, and free.
          If IT refuses to help you, AND to install the self-service add-in, then they really are shooting themselves in the foot.

          - juanito

          • Kenneth says:

            Installing anything, even something as mundane as the powerpivot addon, requires administrator password, which IT holds.

    • Laura says:

      Thank you for the Index/Match comment! My spreadsheet was built over a couple of months, adding new formulas as new requirements arose. This resulted in 86,000 cells with Index and Match. I'm off to clean up some formulas!

  5. Jeff Weir says:

    Kenneth - instead of drawing data from 5 seperate reports, I'd suggest get IT to create a new report that does as much of the number-crunching within SAP as possible. I often see files where most of the formulas are used to combine data that comes from the same database. In those cases, a simple tweak to the SQL query behind the reports makes tens of thousands of formulas redundant. At the same time, it's pretty simple to have SQL create calculated fields for current, 1-30 days past due, 31-60 days past due columns.

    What kinds of formulas do you have in this apart from Index, Match? Anything really resource intensive like SUMIFs etc? Can you post a sample file somewhere, like on the Chandoo forum? I'd like to take a quick look.

    • Phil says:

      Jeff, in theory, that's a brilliat idea. However in my organization this would be impossible. I'm working in a commerical department support the division IT of a global company (400k+ employees). I cannot get them to change anything on the system, not taking into consideration that data may come from several different systems (yes all SAP, but at my company alone we have 70+ SAP systems). I really like Hui's idea of using PowerPivot which could really make things much easier, though..

      • MF says:

        Phil, can't agree more.
        In real world (or in my experience), asking IT to revise something simple is actually difficult. That's why many people prefers Excel to BI tools. And as a result, hours and hours of manpower is wasted.

        On the other hand, although every one knows that Power Pivot is great. Installing Power Pivot into a PC in my office is not easy...

        Sad but True...

        • Kenneth says:

          Good suggestion Jeff, but Phil and MF nailed it. Changes to SAP are projects, and those are a big deal. There's an upcoming SAP module that I actually bulit this spreadsheet to mimic, but it turns out my mimic is significantly better than the module, which is in the final stages of testing and looks like a half-hearted attempt to replicate part of my spreadsheet.

          • Jeff Weir says:

            I feel your pain 🙂

            Another thing you can do to minimise recalc time is to convert any of the formulas in your data sheet - except those in the last row - with values. That way,
            1. Excel doesn’t try to recalculate these formulas (which it does whenever you open or close the file).
            2. When you put new data in the table, it’s very easy to copy the formula down from the last row, and then again then replace every formula except those in the last row with values

  6. Thanks for this Jeff. Its interesting to go to many of the forums that attempt to answer this exact question, yet they don't go into nearly as much detail.

    Example, I often hear to limit the use of SUMPRODUCT. But have never read the reasons that you lay out, nor these preferred solutions.

    Also, good to learn that the delete key on the keyboard behaves differently than delete within the program. Wasn't aware of that. Good tips.

    Thanks
    Rick Grantham

  7. Jeff Weir says:

    Thanks for the kind words, Rick.

    Caveats are sadly lacking from most blogs. "OFFSET is cool, but they are volitile, which means...". "SUMPRODUCT is increadible, but they are resource intensive, which means...". "PivotTables are awesome, but they store a copy of your data in a PivotCache, which means..."

    We educate people on one weapon at a time, but don't give them any basic firearms safety training, nor talk about how to consider which weapon from the armory - or which combination of weapons - best suits a particular set of battle conditions.

  8. Cathy says:

    Thank you for a great post. I am a culprit of Manual calculations on my latest spreadsheet and would appreciate any advice. It falls into the "confused range" discussion. I have approx 10 worksheets (customer) which are formatted identically with rows of sales data, then a main sheet which summaries the 10 worksheets. The 10 customer worksheets all vary in the number of rows so a lot of my formulas are sum(k7:k9999) to allow for the varying no of rows so I don't accidentally exclude rows. How can I apply your discussion of confused ranges to formulas where the row numbers vary? Many thanks

    • Chris Hanson says:

      One thought Cathy, is to change the data ranges into tables. that way you can use table references (structural references). instead of cell ranges. Tables references are dynamic.

      Please take a look at one of Chandoo's prior posts on the subject:
      http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/

      • Jeff Weir says:

        Yep, great suggestion, Chris. Whenever you have Excel 2007 or greater, and whenever you have data sitting in a block, and whenever you point formulas, pivots, charts at that data, then you want to change the data range into an Excel Table with the Ctrl + T shortcut.

        It's a great habit to get into. As soon as you build a new sheet - or are reviewing an old one - then as soon as you input new data into a block - or come across some existing data in a block, then push Ctrl + T.

        Including VLOOKUP tables.

        • Cathy says:

          Thanks Chris and Jeff. So the table will automatically adjust in size? Thanks for pointing me in the direction of tables, it's opening a whole new world.

          • Jeff Weir says:

            Yes they do, which is why they are so handy when it comes to pointing PivotTables at them...you never have to click the 'Change Data Source' button again.

          • Duncan says:

            Not only ... Columns and rows are added as you wish.

            If you link a graph or graphs to a table they update automatically, too.

            Pivot tables are updated as your table expands.

            These tables are so good. I managed a small college's student achievement database in an excel table: 1,700 students, four semesters, six subjects per semester, four assessments per subject per semester. Name, class, father's name, province ... Formulas for pass and fail per semester, attendance percentage ...

            It worked really well.

            I know, should have been in a database but you need to know where we were and the resources we had!

            Duncan

  9. dan l says:

    I'm reading Phil's description and I give it at least a 15% chance he and I work for the same company......

  10. chris says:

    Great tips. Ive read that after the delete action, you might need to go to cell A1 and save the document. Not sure if this helps,but thought i wd mention it 🙂

    Chris

  11. […] Is your Excel file big and slow? Jeff Weir suggests a couple of ways to fix things. […]

  12. VvM says:

    What about the event where it is columns and not rows that are taking up unnecessary space in the used range? I tried modifying the code to correct that, but it didn't do so.

    • PEL says:

      The fix for the extra rows worked great! But now I'm wondering the same about the extra columns. I tried modifying the code as well (just changed the word "rows" to "columns") and it didn't work.

      Anyone know a fix for extra unneeded columns?

  13. Stephen says:

    One other thing that will cause havoc - dynamic functions. In an industrial environment, there are several versions of historians (which collect process data real-time), which usually have excel plug-ins. The problem comes in if you have =TODAY() or =NOW() in those spreadsheets. All of a sudden, you have 20,000 cells recalculating every time you make a change. Better to use VBA to repopulate a cell with the current time and date when you open the spreadsheet.

  14. Mamta says:

    nice information

  15. Martin says:

    @Cathy: My 2 cents of adrice would be to merge all different sheets with Customer data in one single sheet (that should be possible as you state that all sheets have same format).

    Then add a new column (in column A for instance) calling it "Customer", so you have a field to differentiate all 10 imported data blocks with sales data from 10 different clients.

    Next thing is to convert the data range into a table.

    Then create a pivot table based on the created table to substitute the main sheet.

    And "voilá" 🙂 You will have only two sheets (one containing data, other containing pivot with summary), easily maintainable (you can order sales data in data sheet by date or by customer, whatever suits you best).

    Hope these ideas provide you with solutions to improve your spreadsheet design!

    Bar

    Martin

  16. […] me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a higher […]

  17. Vaibhav says:

    Hi Jeff,
    Extremely well written. Just wanted to say that your 'used range' tip (ctrl + end) helped me reduce my workbook size from 70mb to just 6mb! Thanks a bunch!

  18. Donald Kempf says:

    Thanks. I formatted a column and it entered the formula all the way to the bottom of the possible XL page. Plus add 4 pages wide onto that and i now had a large doc. I wasn't able to click on a square without " not responding" showing up.
    Can I in the first place just limit the formula to let's say 500 lines? So I have room to add data without falling off the formula but not blowing out the Size and speed.

    Thanks the general info you supplied was on the mark. The following replays is just over the top. For me that is.

  19. Donald Kempf says:

    The [CTRL] and [down arrow] didn't work for me. I just selected the data area and cut and paste into a new worksheet. I got rid of my extra 1,048,000 of lines.

  20. CHRISTOPHER LEE says:

    Purely rhetorical, at this point, but... why would MS avail capability in software that, when used, makes said software crash or work so slowly that it is next to useless in terms of productivity and efficiency? Array type formulas are useful tools, however, when used tend to gum up the works wrt data analysis and normalisation.

  21. Tim Bailen says:

    For "The Confused range" solution, it is important to SAVE immediately after you delete the extra rows, then close and re-open the spreadsheet. I got this tip from a comment on http://xsformatcleaner.codeplex.com/releases/view/98007#ReviewsAnchor, and it is also mentioned in the Microsoft support article on this issue (https://support.microsoft.com/en-us/help/244435/how-to-reset-the-last-cell-in-excel)

  22. Jamie McIntyre says:

    I know it's an old post, but thank you. Your "confused range" tip just reduced one of my spreadsheets from 21.5Mb to 1.5MB. It obviously is still an issue with Excel 2016.

    In my case, there were thousands of columns with fancy formatting but no data: someone had just copied the column formats across the whole worksheet. All I had to do was delete all the empty columns.

    Fantastic.

  23. Katherine says:

    I have a large dataset which I need to lookup values from. Is it more efficient to have one large named range (identifier code + 134 columns) or to repeat the identifier code multiple times to break it down into smaller named ranges (e.g. identifier code + 50, identifier code + 50, identifier code + 34)?

  24. Karin says:

    Wow, I just squeezed an excel sheet down from 88MB to 250KB using the confUSED range solution you propose. I had to do both, deleting the range first AND run the macro.
    Thank you!

  25. TJ Texas says:

    Thanks for sharing your knowledge. No formulas in my spreadsheet - yet it had bloated to over 145,000 KB. Using the CTL-End determined one of the workbooks had excessive empty rows. Copied the only 27 rows of data from that workbook to a new workbook then deleted the bloated workbook and now down to a slender 64 KB.

    Now how can I do the same when I feel bloated?

  26. massive Chandoo!!! you saved me !!

  27. Jessi says:

    I really appreciate your post!

    My company uses Google Sheets online to maintain spreadsheets with multiple contributers and I'm constantly (almost daily) having to apply what you call the confUSED range fix. I'm so glad it works, but of course no one else will take the time to learn the fix.

    Can you help me with understanding how the bloating happens to begin with? Is it always someone applying formatting to the entire sheet? People in my company act like I'm speaking another language when I try to explain even the simplest of solutions... any help would be appreciated 🙂

    Thanks!!

  28. Matthew Henderson says:

    I'm constantly running into Excel bloating. I'll turn a file into binary and that will fix much of it, but sometimes it doesn't. I just created a new binary file and copied 9 worksheets from the old binary workbook (45mb) into the new workbook. By copied I mean right click on the worksheet and copy to a different workbook. Once I saved the new workbook with the same worksheets, my new binary file went to 3mb. No pivot tables, roughly 85000 rows x 710 columns (with 662 columns blank). Any idea what could cause such bloating?

Leave a Reply