Excel Dashboard Color Scheme Selector

I recently stumbled upon Josh Tapley’s nifty Color Picker for Xcelsius dashboards. In his post, Josh says:

“Many times I have seen themes that I like on Adobe’s Kuler and ColourLovers.com only to find out that they don’t translate well to graph format.”

I completely agree! Being one who’s more than willing to steal brilliant ideas, I thought I’d try replicating Josh’s color picker in Excel.

I created an Excel workbook that allows you to cycle through a set of color schemes to find one that suits your dashboard. Once you have one you like, simply note the RGB values in cells M2:M6, or click the Capture Colors button to have the colors added to your Recently Used Colors panel.

ColorScheme

I even added a table where you can edit the color schemes with stolen your own HEX color combinations.

I’m sure there is a way to programmatically create a new theme based on the chosen colors, but I’m too lazy to figure that out right now.

I left all the code in the workbook open for your reading pleasure.

Feel free to download the Color Scheme Selector workbook.

Happy coloring!

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 3

This is Part 3 of a project where I attempt to create a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. So far, I was able to create a macro that outputs Cell Referenced GETPIVOTDATA formulas for the target pivot table. My last objective is to see if I can bring the Page Fields along for the ride. That is to say, I would like to auto-create slicers to interactively alter the values returned by the GETPIVOTDATA formulas.

The screen capture below shows how Page Filters can be converted to Slicers that work with the auto-generated GETPIVOTDATA formulas.

ConvertPivovtToFormuasl2

You can try it for yourself a sample file.

Download Sample File

All the code for the macro is documented in the sample file, so I won’t bore you with the details. I will say that the tricky part of dynamically creating slicers is avoiding duplicates. Excel seems to freak out if you create a duplicate slicer. So each time the macro is run, we need to check for existing slicers. If existing slicers exists, we copy the slicer and point the copy to our pivot table. Otherwise we can create a new slicer with no issues.

By the way, I need to mention that I made heavy use of the excellent set of articles from Jan Karel Pieterse to get a head start on programming slicers. Check out Jan Karel’s site for all kinds of useful brain candy.

Well that’s it for this little side project of mine.

I would say that this macro has moderate usability. If you need a dashboard model fast, you can create a pivot table, run this macro, and voila! You’ve got yourself a set of formulas that can be moved, copied, and adjusted to create an interactive reporting model pretty quickly.

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 2

Last week, I embarked on a journey to see if I could build a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. I was able to create a macro that outputs hardcoded GETPIVOTDATA formulas for the target pivot table.

In this post, I’ll show you a macro that improves the output to include cell references instead of hard-coded values. With this macro, you can point to a pivot table and fire the code to get a new sheet with a formula driven version of the pivot. Read more

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 1

One of the cool things about PowerPivot and other OLAP pivot tables is that you can convert the entire pivot table into a series of CUBE formulas. With just a single click, Excel will replace your PivotTable with formulas that call back to the Data Model. Converting your pivot tables into formulas basically gives you a pivot table that can be taken apart. You can do things like insert rows and columns, add your own calculations between data items, combine the data with other data on a dashboard, and you can modify the report in all sorts of ways by simply moving the formulas around. Read more

Excel 2016 Function Changes

Hi there! I know it’s been a while since my last post. I’ve been updating several books for the Office 2016 release. I thought I’d pop up and provide at least a glimmer of useful info. I’ll attempt to get back to regular blogging in the upcoming weeks.

Since I’m knee-deep in updating content on formulas, I’d like to share a few Function changes (as far as I can tell) introduced with Excel 2016.

New Excel Functions in 2016

As far as I can tell, only 5 new functions will be introduced in Excel 2016.

These forecasting functions are no doubt included as a necessary part of the new Forecast Sheet functionality.

You’ll find these functions in the Statistical formula category. Read more

Understanding Office 365 Plans

To my dismay, I’m constantly being asked which version of Office 365 is the best for Excel analysts. Like everyone else, I typically provide a semi-vague answer which was most certainly pulled from some website somewhere. Well, I recently stumbled on this nifty TechNet site that outlines the differences between the various versions of Office 365.

.

Unlike the generic comparisons we typically see, Read more

Too Black for Excel

You know what bothers me? As you can tell from the ambiguously racist title of this post…black font.

I know it sounds strange, but the default font in Excel is somehow too black. The contrast of the white background and the really dark black font color annoys me. In a time when we’ve moved away from the standard VB colors, why does Excel default to RGB(0, 0, 0)?

.

Lately, I’ve made it a point to tone down the default black color in my spreadsheets. Although the difference is subtle, I think my reports look better both on the screen and in print form.

.

For example, in the screenshot below, Read more

Using the Immediate Window to Get a List of File Names

Here’s a trick to get a quick list of files from a certain directory.

Open the Excel or Access Visual Basic Editor and then press Ctrl+G to see the Immediate Window.

Enter the following syntax and then press Enter. Be sure to change the directory (in red) to the path of your directory.

File = Dir(“C:\MyDirectory\“): Do Until File = “”: Debug.Print File: File = DIR(): Loop

.

Your reward will be a list of all the files in that directory. You can now copy this list and use it as you see fit. Read more

Ten Simple Tricks to Speed up Your Excel VBA Code

I’m currently on a project to improve the performance of a particular workbook.

Part of this task involves speeding up a few of the macros in the workbook.

While I’m thinking about this stuff, I’d like to share a few simple tips for speeding up your Excel VBA code.

Some of you will know all of these and more. So feel free to comment and share any other performance tips you may have.

.

Halt Sheet Calculations Read more

VBA to See Who Last Updated a Workbook

Have you always wanted to prove that your co-workers are morons intent on making you look bad? Well today, I’ll share a trick that will make all you paranoid schizophrenics very happy.

.

A friend of mine asked if there is a way to easily see who last updated a particular workbook.

Apparently, he works in an environment where multiple people are editing workbooks on a share drive.

Of course, he knows that the workbook properties will show who last saved changes, but he wants to see that info as soon has he opens the file.

My answer to him was to hijack the Excel Title Bar. The Title Bar usually only shows the name of the workbook. But with a bit of simple VBA, Read more