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!
Pages to are hidden for
"Anatomy of Excel Formatting - PDF"Please download to view full document