Anatomy of Excel Formatting - PDF

							Excel
Anatomy of a spreadsheet makeover…
I was contacted by an internal client with a problem:
How could we get an Excel spreadsheet to replicate data on sheet #1 to sheets #2 and #3?
Simple request, right? The short answer would have been use pasted links. But this answer would not really
have been solving the problem. I had to recognize that my client might not recognize the extent of the problem
and could possibly require education on the workings of Excel.
In probing deeper to discover the intent behind the request, we soon learned that there was a far more efficient
method of setting up the spreadsheet than what was currently being utilized. The first step lay in removing the
need for three separate spreadsheets.
Here’s the way the spreadsheets were originally set up:
                   NAME        DEPT              LOCATION # PROJECTS REVENUE
                   John Doe Laboratory           Naperville    75                 $250,000.00
                   Jane Smith Chemical, Fire Naperville        80                 $325,000.00
                              & Explosion
                   Total                                       155                $575,000.00


                   John        Materials         Ann Arbor     15                 $100,000.00
                   Public
                   Sally Jones Mechanical        Ann Arbor     12                 $75,000.00
                   Total                                       27                 $175,000.00




In addition, a second spreadsheet sorted the group by revenues and a third spreadsheet organized the groups by
department.
The first question I asked… Do you really need those grey lines separating sections? The answer was a
reluctant, “Well maybe not. Why?” By making a few simple changes to the way the spreadsheet data was
arranged, we were able to make some BIG changes in this client’s efficiency.
Here’s the new spreadsheet I created.
        BUS TEAM FIRST NAME LAST NAMEDEPT                            LOCATION PROJECT REV
        ABC          John               Doe             Laboratory Naperville       75          $250,000
        XYZ          Jane               Smith           Chemical,    Naperville     80          $325,000
                                                        Fire &
                                                        Explosion
        XYZ          John               Public          Materials    Ann Arbor      15          $100,000
        ABC          Sally              Jones           Mechanical Ann Arbor        12           $75,000
Doesn’t look a whole lot different, does it? However, here’s what it allowed my internal client to do:


       What New Setup Can Do                            Why Old Setup Couldn’t Do It
       Sort by Last name, Center of Excellence,         Sorting functions only work within a range (a
       Location, # of Projects, Revenue, or Business    range is data bounded by blank rows or
       Team                                             columns). By inserting blank rows between
                                                        ranges, sorting would no longer work effectively,
                                                        without messing up the sections.
       Use Subtotals, which automatically create        Subtotals work within ranges. The blank rows
       sums (or other mathematical functions) at text   between ranges prevented this feature from
       changes (i.e. at each location change or each    working, as did the inability to sort the data into
       center of excellence change). My client had      the necessary order (subtotals work top to bottom
       inserted a blank row and then manually           at changes of text). Also, by manually including
       inserted a formula at the bottom of each range   formulas to add the totals, chances of human
       to add up the revenues.                          error were increased.
       Use Data Filters, which allow you to hide        Data filters only work within one range. Every
       rows that don’t meet certain conditions. For     range would need to have separate filters applied
       example, you could view on your screen only      (a royal pain) and the results of the filters would
       Ann Arbor or only Materials. You could also      only reflect that range.
       do custom formulas, such as revenues in
       excess of $50,000.
       Create Pivot Tables and Charts – These are       Although Pivot Tables can consolidate data
       summary tables and charts of your data. For      across multiple tables, by arranging the
       example, if you wanted a table that              spreadsheets more logically, we were able create
       summarized the number of all projects by         pivot tables instantly.
       center of excellence or by business team, you
       could get that using a pivot table, instead of
       individually adding the projects.
       Use AutoFormat, which applies pre-designed       AutoFormat only works within a range. Each
       formatting to your spreadsheet, such as          additional range would have to be AutoFormatted
       having every other line of text be grey to       separately.
       make it easier to read across the line.


There were actually even a few more features that could be accessed now, but they weren’t immediately
needed.


What else did we do to make life easier with this spreadsheet? We added macros for each of the different sorts
that were needed and then put them on the toolbar so that only one mouse click was needed for each type of
sort. We also added buttons to turn Data Filters on and off.
                                      The result, in the client’s own words:

                                                WOW!