							Contact Us! California (408) 835-8436 Rhode Island (401) 580-5289 Questions? Comments?
Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	About Us
Search Our Site:

Making Excel Table and Chart Updates Quick and Painless

Part 1: Where do We Start?

Excel tables and charts are routinely subject to frequent and/or periodic updates as the data changes, more data becomes available, or simply because the period dates have changed. The repetitive tasks often employed for updates can consume exorbitant amounts of time, causing frustration and aggravation. Fortunately much of this can be alleviated by following a simple workbook design approach and taking advantage of select Excel tools and functions, particularly "dynamic named ranges". Such workbooks are not only painless to update, but are also more accurate and easier to check.

This is the first installment of a multipart series detailing how to set up a "dynamic" workbook. The word "dynamic" is used here to imply that the workbook adjusts itself as the data is updated and therefore minimizes the effort and time required for updates. At the end of this series the reader will have a familiarity with a set of Excel tools and functions which can be employed to make any workbook dynamic, either in small ways to facilitate one aspect of a workbook, or to make holistically designed workbooks that are completely and synergistically dynamic.

Note: this whole series is being presented as a CPE credit granting webinar through CPElink.com on October 28. It will also be offered as a free Second Life presentation on October 15th at 9AM Eastern and again at 4PM Pacific.

The presentation can be given upon request in person as a lunchtime Brown Bag seminar in your company's training or conference room.

Demonstration File

A demonstration file has been created to illustrate the employment of the techniques described here. This file is available upon request. Please contact us if you would like a copy. The work task addressed in the example is the creation of a Profit Loss table which is to be updated gradually as the data becomes available or changes. The table below is the Summary Report Table, which holds that major categories.

Data Storage vs. Data Presentation

A fundamental aspect of setting up a dynamic workbook starts with the first step: how to store your data. In our example we will be keeping our data on a separate worksheet from the worksheet which holds our report tables. This approach has many convenient repercussions that we will take advantage of further down the design road.

Below we see a snapshot of the worksheet holding the raw data. As you can see we have the Year all the way to the left, followed by Type which clarifies if the data contained in that row is budget data or actual data. The columns to the right (not all are visible in the figure) contain raw data and summations for each category: Net Sales, Cost of Goods Sold, Total SG& A, and Net Profit. As new data becomes available the values are entered here and/or adjusted as necessary. Current year's data is entered at the bottom and creates a running mini "database" for all recorded years.

Parallel Design

The next approach we will employ to create a dynamic workbook is to match the column/row order of the headers between the Data table that holds the data and the Report table that presents it. As we will see later this will allow us to more easily create the formulas required to pull the data from the storage table into the report table. The table in the figure below is the Detail Report Table. As you can see the order of the items down the rows in the Detail Report Table match the order of the columns in the Data Table.

In our next installment of this series we will see how we can take advantage of these simple design methods and begin to make the Report Tables, both Summary and Detail, automatically update as new data is added to the Data table.

Happy Spreadsheeting!

References

Journal of Accountancy Articles

Tweaking The Numbers

Block That Spreadsheet Error

Excel Security Issues

Clients

Do you need help with Microsoft Office? Contact us

See how we've helped companies like yours develop effective business processes

Client Success Stories

Consultants

I Get It! Consultants set their own hours, work with their own clients, and choose their own projects.

If you're a Microsoft Office guru and would like to explore being a full-time consultant, please see our consultant information pages.