For business users of Microsoft Excel Free guides and templates

Career Advancement

How to Disguise Your Company-Private Excel Dashboards So You Can Show Them to Others

Here's how to disguise your company-private Excel dashboard reports so you can demonstrate your expertise in Excel reporting to people outside your company.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

An Excel dashboard report would be a great addition to your resume, for at least four reasons.

First, it would make your resume stand out like a beacon in a gray sea of ordinary resumes.

Second, it would demonstrate valuable Excel skills that few other Excel users could offer.

Third, if you understand what the data in your dashboard actually MEANS, your report could serve as a reason to discuss the data…giving you the chance to demonstrate your professional expertise.

Fourth, your dashboard would demonstrate an immediate benefit that you could bring to your prospective employer.

Unfortunately, using Excel dashboard reports like this is difficult because they typically display company-private information. So if you were to distribute an actual report publicly, your prospective employer wouldn’t trust you and your previous employer could retaliate.

But there’s an easy solution to this problem.

In this article, I’ll show you how to disguise company-private information in your Excel dashboards so you can show off your Excel-reporting skills to others. If you’d like to follow along, you can download the sample Excel dashboard workbook for free at this link.

To see how this method works, suppose that this is the top of your company-private report…

Original version of an Excel dashboard

If you want to show this report to others, you quickly could disguise it to look like this…

Disguised version of an Excel Dashboard

Here, all identifying information about the original company has changed: the report title, the short note at the top, the magnitude of the data, the chart titles, and the section name. But both the report format and the patterns of the data remain unchanged.

Here’s how to set up your disguised version…

Step 1: Save a Copy of Your Original Report.

The sample dashboard workbook includes all its data in the same workbook. But all of my standard dashboard reports maintain links to one or more external data workbooks.

In either case, save your report workbook as a separate file so that the changes you make won’t damage your original report.

Step 2: Change the Report Title and Notes

The top of the Control sheet for the original Excel dashboard.The Control sheet in your report workbook contains settings and calculations used throughout the report. The top-left corner of the Control sheet contains an area like this.

Here, you can change the four items in the yellow cells shown. Typically, there’s no need to change the current period in your disguised report, but you’ll definitely want to change the report title and other information.

Step 3: Change the Figure Titles

Each figure in most Excel dashboards gets its data from a worksheet set up to support the figure. These sheets are called Figure Data Support (FDS) sheets.

Original Figure Data Support sheet for Figure 1 of the Excel dashboard.This figure shows the top-left corner of sheet D, the FDS sheet that supports Figure 1 in the sample dashboard report.

Cell B3 contains the default lookup code, which could be a SKU, a General Ledger Account Number, an employee code, or whatever. But in this case, the DefaultCode cell contains a product code.

Disguised Figure Data Support sheet for Figure 1 of the Excel dashboard.The formula in cell B4 looks up the DefaultCode in the data worksheet and returns the product title. Cell B5 gives you the opportunity to replace this title with another. And cell B6 returns the title that the figure actually will display.

After you enter your own title in cell B5 of the first FDS sheet, be sure to make similar changes to each NewTitle cell in each FDS sheet of your disguised report workbook.

Step 4: Change the Scale Value

The scale setting for the original figure 1.Cell B7 in this figure contains the label that appears in the lower-left corner of the first figure of the undisguised Men’s Cellar dashboard. And cell B8 contains a scaling value in sync with the label.

In other words, all values displayed by the chart are multiplied by the ScaleReport value of .001, and then the UnitsLabel informs readers that the chart displays Thousands of $.

In regular dashboard workbooks, the UnitsLabel and the ScaleReport cells are calculated automatically. But in the sample dashboard, you update these cells manually.

In the disguised version of your dashboard workbook, change the ScaleReport cell in an FDS sheet from…

B8:    0.001

…to…

B8:    =0.001*1.789

The scale setting for the disguised figure 1.…where the value 1.789 is a small, randomly chosen number that will disguise the magnitude of your data. This will give you a result that looks like this figure.

If you have a regular dashboard report, the top-left corner of your FDS sheet will look something like the following figure.

Disguised version from a regular Excel dashboard.Here, the UnitsLabel and the corresponding ScaleReport values are calculated automatically.

So in this case, change your ScaleReport formula from…

B13:    =INDEX(Scales,ScaleRptType)

…to…

B13:    =INDEX(Scales,ScaleRptType)*1.789

…where, again, the value 1.789 is a randomly chosen number that will disguise your data.

After you make these changes to the first FDS sheet, be sure to make similar changes to each ScaleReport cell in each FDS sheet of your disguised report workbook.

Step 5: Change the Section Titles

Take another look at the top of the original, undisguised dashboard report:

Original version of the Excel dashboard

The “Outerwear” label is plain text that you can move, change, or merely delete. If your original report uses that label, you’ll probably want to change it, as I did here:

Disguised version of the Excel dashboard report.

Step 6: Save and Distribute Your Disguised Report

The easiest ways to distribute your disguised report electronically is to copy and paste an image of your report, or to save it as a PDF file.

To copy an image, select the entire area of your report, and then choose Home, Clipboard, Copy, Copy as Picture. In the Copy Picture dialog, make sure As shown on screen and Bitmap are selected. Then choose OK to copy the image of your report to your clipboard.

(Note: When you copy a bitmap of an area surrounded by a border, Excel excludes the border at the left and top of the area. To get around this problem I typically add a narrow column to the left of my dashboard and a narrow row above it, then I include those narrow areas in the area that I copy.)

To save your dashboard image, open a new workbook; select cell A1; press Ctrl+v to paste the copied image; and then save your workbook using any name you want. Also, of course, you could paste your dashboard image to a Word document.

To save the image as a PDF file, activate your report worksheet, then choose File, Save As to launch the Save As dialog. In the Save as type dropdown list box, choose PDF (*.pdf). Give the file any name you want, then choose Save.

Again, if you want to experiment with a simple Excel dashboard report, you can download the free dashboard workbook here.


Tags: #dashboard, #resume, #disguise, #download, #career

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.