I had an email exchange with Charley Kyd tonight, and I was bemoaning the fact that I couldn't link my Excel chart titles to a range. I could have sworn you could do this, but not working with charts a ton, I just could not find a way to do this in the Excel 2010 user interface.
To me, the logical place for this kind of functionality would be in the "Select Data Source" userform. This would seem a no-brainer since it's where you link up your series, define your chart range and axis labels as well as your legend. Why wouldn't you be able to link your title here if it was possible?
Well, apparently, just because it isn't in a wizard or a userform doesn't mean it can't be doneâ€¦ and done without resorting to VBA.
What I was looking for specifically was to give the following chart a dynamic title.
The data in the table is dynamic, and reflect the product line. So depending on a data validation list, I could be showing Beer, Wine or Liquor sales. Based on what Charley told me, I set up a little matrix under the chart:
Select the chart title
Click on the cell that contains my new dynamic title
And likewise when I change it to reflect Beer:
So am I the only person who didn't know this? It's not really that intuitive is it? (Thanks for the pointer, Charley!)
I've been pretty up front with the productivity gains that I saw when I embraced VBA years ago. In fact, my About Me page on my website spells it out pretty clearly:
In the fall of 2002, the resort's management company decided to cut labour costs in the administration department. Overall, 2.5 full time equivalent positions were cut out of a 7.5 person office (yes 33% of the labour used) within one month, with the department expected to produce the same amount and quality of work. During a desperate shuffle of tasks to other departments, and dropping of tasks that were low priority, I realized that automation was going to be the only answer.
From there, I launched myself into VBA (Excel's programming language) full force. Automating as many tasks as possible, the administration office has been able to perform virtually the same task load as before the cuts, and the annual labour savings can be estimated somewhere between $20,000 and $40,000 per year. Suffice it to say, I love VBA, and am still amazed by the things it can do.
Revolution #1 - The Power of VBA
My embracing VBA caused some revolutionary changes in our processes. Many of the macros I wrote I now consider rather basic, but they made a huge difference to our productivity. We do a full working paper file set every month end, reconciling about 100 accounts monthly. Our first macros were controlled by two buttons in each file and basically allowed us to automate the following:
Copy month end balances to opening balances
Clear out the data entry fields
Update the date to a new month
Save the file under a new (correct) file name
Created a folder for the new file if one didn't exist
Printed a copy of the journal entries (but only if required)
Printed a letter sized copy of our leadsheet (if required) to attach to the JE for support
Printed a legal sized copy of the leadsheet
Printed any supporting schedules (inventory listings, etc..)
Now some of the files were a bit more complicated, but even that simple level of automation allowed us to do a huge amount more work. The macros were consistent, eliminating much user error, and quick, allowing us to focus on doing work, rather than manually rolling things forward.
A specific case of needed improvement
For the last few years we've been working with these macros, trying to figure out how to amp some things up and become more efficient. Over the past couple of weeks I've spent some time working on a file that has been ticking along since the early automation days â€“ one of our liquor inventories.
In the past, this inventory's process was as follows:
Open the Excel file
Click the RollForward button
Scan the inventory counts using a handheld scanner
Departments also record the inventory date and some counts in the Excel file directly
Upload our handheld scanner counts into an Access database (that I built)
Print an inventory valuation report from the Access database
Manually enter the inventory closing balances into the Excel file
Print the general ledger listings for the appropriate revenue and expense listings (6 product lines)
Manually enter the current month transactions into the file
Manually enter the prior month closing balances into the file
Manually enter each day's sales for the dates between the count date and end of the month
The whole package is scanned and sent to the department for review
I'll be completely honest, the file is weak and has long needed some better information in it to help with the review portion. The problem has always been getting good information into the file efficiently in a usable format. With the volumes of other work that are going on, it's simply been too much work to do.
Enter PowerPivotâ€¦ and the next Revolution
In case you haven't worked it out yet, PowerPivot is allowing me to start taking this file to the next level. I've been waiting for this for a long time, and some of my dreams are finally coming to fruition with this new technology. So what did I do?
I've added a PowerPivot database to my file. This database pulls information from 4 different sources; 2 Access databases, a SQL Server database, and an Excel list.
In fact, there are actually three separate tables sourced from my BI Database: Chart of Accounts, Budget_Ops and Budget_Internal.
So what, right? Well here's why it's all important. Remember how I had to print my general ledger listing, walk to the printer to get it, then enter it manually enter the number into the spreadsheet? Not any more. Now I just hit Refresh. Why? Because the PowerPivot database is an OLAP database, which means we can use Excel's CUBE functions to pull data directly from the database via a formula. In fact, this formula returns the value of my beer sales for the current month:
CUBEMEMBER("PowerPivot Data","[Measures].[Sum of Amount]"))
This fixes a major problem that I've had in the past. No more fat-fingered entry of numbers, no more forgotten entries. And even better? I can use a similar formula to pull out the value of the Beer inventory for the month. Mark the importance of thatâ€¦ it's from a different database.
I've also now loaded up this file, not only with the closing value of the inventory for one of my report tables, but a full inventory listing in a Pivot Table. No longer do we have to run the inventory listing from Access and email it down with the Excel file. It's embedded.
I've also put in a G/L Listing in a Pivot Table as well. This was never there before either. It actually makes use of a relationship that I defined in PowerPivot between the Chart of Accounts (from my Access BI database) and the Jonas Actual Transactions table (from my SQL database), as well as a relationship with a table of dates from an Excel worksheet. The relationship allows me to display the data with the account names, and drive the drilldowns via date slicers.
But the crowning achievement to me is that I now have access to my budget information in addition to the rest. This means that I can now provide a full dashboard of micro graphs that tells us what is happening in the department. The process of sourcing this data in the past was just far too painful to consider.
So from a rather stripped file that reported numbers in an ugly accounting format, we're turning this into an intelligent application capable of identifying why there is an issue in the inventory. (G/L Listing, Inventory Listing, Micro-graph dashboard, and all delivered quickly without having to enter information manually.) My team member who works on the inventory with the department figures that this will save him about 1.5 hours per month in processing and investigation time. While it may not seem like a lot, this is HUGE to us. It's 1.5 hours when we need them the most.
Is it all roses?
Lord no, not at all. I have an active thread open in the MSDN forums, trying to work out how to use a date/time DAX formula in a PowerPivot measure. The point of that is that I want to be able to use "OpeningBalance" as a measure in my formula:
This is not easy stuff to me at this point. (I'm kind of hoping that I'll look back on this and laugh in about 6 months.) I'm still very much learning how to work with PowerPivot here, but this has huge potential.
Could I have done this without PowerPivot?
I should be up front here. Some of what I'm doing now could have been done outside PowerPivot. If I had solid SQL skills, I could certainly have created custom SQL queries and leveraged them against each databaseâ€¦ at leastâ€¦ I think I could. I do still write some of my SQL to try and get the data source as narrow as possible, but I'm not sure that I'd be able to write the SQL to link the account names with the account numbers from my Jonas (SQL) database. If I could, then I suppose I could have hooked up a PivotTable for each query and used GETPIVOTDATA formulas to pull the data out.
Despite the fact that I may have been able to pull this off without PowerPivot, this approach is WAY easier. Plain and simple: I don't need to be a SQL expert. That's the whole point to this productâ€¦ it puts the ability to mine data into the hands of business pros, rather than IT experts.