more-arw search

Q&A Forum

Excel 301 Question

I am using a very large excel file to do our annual budgeting (yes, a program is on tap for future years). We have turned off the automatic calculate formula and either use F9 or save to force an update at various points in time to our work. We are finding that some cells on some worksheets won't update with either F9 or save, and we have to go cell by cell to update them. Is there something we might have done to the file to create this problem? We haven't noticed any similarities between the cells that are calculating when prompted and those that will not. However, you can imagine the frustration when we finish a large worksheet that ties into another, hit the save key, and see no changes. The first time this happened to us, it took quite a while to figure out what was wrong.

Answers

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

A popular solution is to close and reopen the workbook, perhaps even after restarting your machine if reopening the workbook didn't do the job.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

A couple of thoughts, and an answer...

Thought 1: Excel formulas *should* recalculate if they reference data that has changed since the last recalculation. If that's not happening, I'd be interested in seeing an example of a formula that isn't recalculating when it should.

Thought 2: Does your workbook use User Defined Functions (which are written in VBA)? Sometimes, they can have recalculation problems (which are easily correctable).

The answer: A sure-fire way to recalculate a worksheet is with search and replace. Just search and replace "=" with "=" (without the quotes). By replacing equal signs with equal signs, you do the same thing that you would do if you edited and re-entered every formula.

Warning: Be sure that you press F9 after you take this step. That's because search-and-replace won't calculate your formulas in the proper order. But F9 will.

Charley

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

I'm not sure if everyone realizes that Charley Kyd is one of the world's foremost experts on Excel and teaches an Excel webinar series here at Proformative.

The first in the series is available for instant viewing via on-demand video and is titled,
"Leveraging Pivot Charts with Excel Tables to Deliver Interactive Excel Dashboards:"

https://www.proformative.com/events/leveraging-pivot-charts-excel-tables-deliver-interactive-excel-dashboards

Also, here's a treasure trove of free excel spreadsheets, free accounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

https://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Shortcuts Cheat Sheet"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Sara Voight
Title: Controller
Company: Critical Signal Technologies, Inc
(Controller, Critical Signal Technologies, Inc) |

Thank you for your input. The find/replace is the answer for the short term and is saving us a lot of grief.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Great, Sara. But I'm still concerned that you had this problem in the first place!

Another thought with regard to your very large file: I always try to put data into one workbook and the reports/analyses in other workbooks. That allows for any number of reports & analyses to reference the same data. It's something you might want to think about.

Warning 1: Never, NEVER reference data in an external workbook by cell address. This is because if you insert a row or column in the data workbook while the report workbooks are closed, the reports will then reference incorrect ranges. Instead, reference all external workbooks by range name.

Warning 2: Only reference *data workbooks* externally. If you reference other reports, you could create some really nasty problems with data integrity.

To illustrate, I once consulted at the headquarters of a company you've heard of, where I found a legally important workbook that violated this rule. It linked to other workbooks that linked to other workbooks, and so on. I found more than 50 inter-linked workbooks, including circular calculations that were eight workbooks deep. I also found links to workbooks on the computers of people who hadn't worked in the company for more than two years.

But if you link to data workbooks correctly, you can keep your workbooks small because you don't have to cram every report or analysis into the one workbook that contains your data.

Charley

Joe Call
Title: CFO
Company: Herrick Industrial Supply
(CFO, Herrick Industrial Supply) |

Charley, What do you mean by "referencing 'data workbooks' externally"?

Steve Breitman
Title: President/CEO
Company: Mindful Business Solutions
(President/CEO, Mindful Business Solutions) |

You might want to upgrade the amount of RAM your computer has. If you have several windows open and other programs running while you work on the spreadsheet, you could run into issues. I, too work with large spreadsheets. I recently upgraded my system to 16GB and don't have any issues.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Joe,

An "external reference" is where in workbook D you reference an area in workbook A. I **only** do this when workbook A contains a table of data that I can use in workbook D. (That is, I *never* link to another report.) With this approach, I also could reference the data in workbook A from workbooks E, F, G, and so on.

I also could maintain other kinds of data in workbooks B and C, which I also could reference in workbooks D, E, F, G, and so on.

The primary ways to return data from the data workbooks are...

1. Using PivotTables.
2. Using worksheet functions like SUMIFS, SUMPRODUCT, and INDEX-MATCH.
3. Using array formulas.

This is a very powerful design, because it allows you to have any number of workbooks for reporting and analysis that contain **no** data. Instead, they have links to data in the data workbooks. With this approach, you update a report merely by changing its report date in one cell...assuming the new period's data has been added to the data workbook(s).

If you want to deliver an interactive workbook to someone, you would have two choices. First, if your data workbooks are available on the network, you could just deliver a report workbook and let it grab the data it needs.

Second, if the report won't have access to your data workbook(s), you could combine the data and the report and then save it under a new name for distribution. One of the problems with this approach, however, is that you lose control of your data. (That is, over time, you wind up with many versions of the truth.)

Does that make sense?

Charley

Michael Filiatrault
Title: Associate Director, Financial Planning a..
Company: Merck
(Associate Director, Financial Planning and Analysis - US Market, Merck) |

If you have a large amount of formulas in your file where the values stay largely unchanged you could try converting the cells to values. I did this in a planning and reporting excel spreadsheet and it cut the size of the file dramatically, increased the speed of the file, and fixed a lot of the problems associated with an excel file that was too much for my computer hardware. If you periodically need to update the values, save a row of formulas, cut and paste in to update all of the rows, then convert back to values.

J.G. Collins
Title: Managing Director
Company: The Stuyvesant Square Consultancy
(Managing Director, The Stuyvesant Square Consultancy) |

This goes without saying, but just in case: be sure to protect all of the formulae in your spreadsheet and password protect the sheet itself. I've had clients who have wanted to use "hard" number entries for various purposes (to be expeditious, or to do instantaneous "what ifs" etc.) so they unlocked cell protections. Invariably, they call a week or two later when the "real" report is needed and their workbook isn't working. Always keep blank backup copies when you have finished (or commissioned) a complex workbook!

Patrick Rutherford
Title: Financial Analyst
Company: Bradford Group
(Financial Analyst, Bradford Group) |

A simple solution might be to press Shift+Ctrl+F9 instead of F9. This will do a full calculation, which rebuild links to external sources. Thus is will take longer so you might want to grab a cup of coffee while its working.

2383 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.