This article is written by Alex Kerin from Data Driven Consulting.
“When expensive dashboard software doesn’t work, do it with Excel” stated Stephen Few back in 2006. This was before the release of Tableau, and some of the other solutions now available for visualizing your data, but Excel remains a great choice for creating dashboards when you extend it with sparkline add-ins, clever chart hacks, and VBA or (relatively) simple formulas.
Excel however isn’t regarded as a “serious” business intelligence tool for delivery of your metrics and charts. Perhaps some of this is that users expect dashboards to be deployed on the web not on a locally installed application.
Today we will learn how to export excel dashboards to web pages.
Exporting Excel Dashboards to Web:
When it comes to exporting dashboards to web, 4 options come to my mind. I’ll quickly review these, culminating in a look at a new option – Excel 2010 and Microsoft’s online version of Office – Docs.com
- Save your workbook as a web page. Text in cells is converted to text in html tables, while charts and other shapes are converted to images. Excel offers two formats – mhtml and normal html. The mhtml version saves as a single file with the images encoded as text. Only IE can natively handle mhtml file while plug-ins exist for some of the other popular browsers. The normal html file option creates a folder full of images. By choosing this option you obviously lose any interactivity you had via drop down forms, pivot tables, or VBA. The conversion of charts/shapes to images is imperfect, leading to fuzzy edged images, and in my (beta) version of 2010 sometimes resulted in blank or missing images. You also lose font type information in 2007, so if you’re using any special fonts to create in-cell charts they will end up looking odd. Excel 2010 preserves font information (as long as the font is installed on the user’s machine). Personally, I don’t think it’s a great option – you may as well just:
[click here for a larger version] - Take a screenshot and post it to your website. You could use contol-printscreen to take a snapshot of your dashboard. As this just dumps the screen to the clipboard you may want to use a screen capture tool which can select a portion of the screen and save it to a file easily. You’ll want to make sure you crop appropriately, for looks, and so that your boss doesn’t see your taskbar with your browser on Facebook. Excel 2010 has a new screenshot option, but that’s for inserting screenshots, not saving them out. You can’t save a file as jpg/png like you can in Powerpoint, but you could save as a PDF and upload that. But what if you want more interactivity?
- Publish using Sharepoint. Sharepoint is an MS server platform that among (lots of) other things, allows publication of Excel workbooks to the web. While some interactivity is preserved (like pivot tables), many features are not (VBA, form dropdowns, images, and shapes). As some sparkline add-ins use VBA to generate a shape that depicts the data, even the static shape will not be shown as it will be stripped out. Other add-ins use VBA and a special font to depict the shape. As Excel 2010 preserves font information, these may show on a Sharepoint server, assuming the user has installed the font. Of course though, they will not update as VBA is not allowed. Linking to external data sources is allowed, so you can use your OLAP cubes or whatever else. Sharepoint is a viable option, but requires servers and licenses, neither of which come cheap. What other options are there then?
- Docs.com and Excel 2010. Docs.com is Microsoft’s online version of the Office applications. At the time of writing (July 2010), it was still in private beta. Oddly, MS has chosen to release it with deep ties to Facebook (login, posting to your wall, and sharing documents amongst friends). I honestly have never needed to share a document with a friend, and equally I’m not friends with the people that I do want to share documents with. Despite this (and I’m sure plenty will change as Docs evolves past beta), Docs.com offers some interesting opportunities for web deployment of dashboards. It still suffers from no VBA or ability to show shapes – I suspect that docs.com is running in a Sharepoint environment, BUT, and this is a big but, Docs.com was built with Office 2010 in mind. This means that the sparklines new to Excel 2010 show up, and update when values change. Take a tour of one of my workbooks on docs.com here.
Problems with Excel Dashboards uploaded to Docs.com
You’ll see on the example that there are several warnings thrown up – I left some shapes in the file before uploading just to show you what the warning message looked like. Linking to external data is not allowed (as you would expect, compared to Sharepoint where you control the servers), so you’ll have to be clever about how you update the dashboards. If your goal is to deploy using Docs.com, you’ll probably design your dashboard with this in mind, making good use of pivot tables for example.
I could envisage using Docs.com in the following manner:
- Develop a great dashboard in 2010 that instantly makes the user aware of any problems (but you’re doing that already aren’t you?) and upload it to Docs.com
- Share the document with your users, and upload a new file as data is added to the dashboard
- For any interactivity (e.g. simple data exploration to further investigate problems), the user can download the document – even though things like dropdown list boxes are not shown on docs.com, they are preserved and will show and work on the local version (VBA is still a no-no as you can’t upload a xlsm file)
I would like to see some changes with Docs.com – for example being able to hide the “Who you are sharing this with” column, allowing full-screen viewing of just the sheet, and sorting out the sharing outside of Facebook.
Now this method will not be suitable for sensitive information where deploying to the cloud (albeit with careful sharing of access) would not be appropriate, but the concept of using Excel 2010 and Docs.com offers some interesting opportunities for web-deployed Excel dashboards, and for using on websites that teach us to become awesome in Excel…
Added by Chandoo:
How do you Export Excel Dashboards to Your Audience?
Do you save the dashboards as PDFs or email the workbook or save as web page? What is your way of exposing the dashboard to the audience?
Please share using comments.
More Resources on Dashboards:
Checkout our dashboards page which has lots of links, templates, downloads and tutorials on creating excel dashboards.
Thank you Alex
I thank Alex for sharing these beautiful ideas with all of us. Exporting dashboards is a growing need and we all could use help like this to become better. Thank you Alex.
About Alex Kerin:
Alex runs a kickass consulting business at Data Driven Consulting. He shares a lot of innovative ideas and information on dashboards, visualization and Excel thru his blog regularly. And of course, he is awesome with excel.
15 Responses to “Highlight Employees by Performance Rating – Conditional Formatting Challenge”
While this might solve the question Shelly asked, there is another option that might be more useful - a pivot table could make a list of people who fall into the various categories, so, if you needed to simply see who got in the top bracket to give them a bonus, you would have that list
Simply sorting by the rankings would work too, but you would knock them out of alphabetical order.
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
The solution I chose makes use of the percentile formula.
The percentile formula returns the value representing the K-th percentile of a range of values. The range of values is the first criteria, and K is the second criteria in the formula.
I applied Conditional Formatting according to the formulas in the order below:
5% =$C6>=PERCENTILE($C$6:$C$33,0.95) Dark Blue
15% =$C6>=PERCENTILE($C$6:$C$33,0.85) Light Blue
65% =$C6>=PERCENTILE($C$6:$C$33,0.1) Green
10% =$C6>=PERCENTILE($C$6:$C$33,0.05) Light Red
5% =$C6<PERCENTILE($C$6:$C$33,0.05) Dark Red
The issue I noted with this approach is that Zambi was not highlighted in my solution as it is in the solution provided. Unless I am mistaken, and I very well may be, the 10th percentile for this data set is at 2.21, so Zambi would fall above the 10th percentile with a PR of 2.3.
The first step to this was figuring out the 'buckets'; what scores should fall into each range. In attempting to match the formatting of the spreadsheet, I determined the buckets below.
5% = 95% to 100%
10% = 90% up to but not including 95%
65% = 10% up to but not including 90%
10% = 5% up to but not including 10%
5% = under 5%
After that, it is a relatively simple matter to plug the necessary values into the conditional formatting formulas as shown above.
One final consideration is that while the buckets above match the color banding on the spreadsheet, I believe that the original request suggests a different color banding with 6 buckets shown below.
Top 5% = 95 to 100% Dark blue
Top 10% = 85 up to but not including 95% Light blue
Top 65% = 35 up to but not including 85% Green
Bottom 10% = 10% down to but not including 5% Light Red
Bottom 5% = 5% or under Dark Red
This leaves one final bucket of 10 to 35% (exclusive of both values) that is not highlighted and so would remain white.
Thank you Chandoo and Shelly for an interesting and useful exercise. This is certainly a valuable technique to have in my reporting bag of tricks.
Use of PERCENTILE is a smarter way of doing it. Below is my solution.
First 5 % = Apply conditional formatting (Dark Blue) as highlight ">=" =PERCENTILE(C:C,0.95)
Next 15% = Apply conditional formatting (Lighter Blue) as highlight between =PERCENTILE(C:C,0.95)-0.01 and =PERCENTILE(C:C,0.8)
Next 65% = Apply conditional formatting as highlight (Olive Green) between =PERCENTILE(C:C,0.8)-0.01 and =PERCENTILE(C:C,0.15)
Next 10% = Apply conditional formatting as highlight (Lighter Red) between =PERCENTILE(C:C,0.15)-0.01 and =PERCENTILE(C:C,0.05)
Bottom 5% = Apply conditional formatting (Red) as less than =PERCENTILE(C:C,0.05)
I agree, this is a challenge faced by HR managers every year and use of percentile formulae is the most popular solution which permits further processing like making bell curve, applying increments based on segmentation etc.
Hi Chandoo,
I came at the same solution as yours (not looking at yours first) but I have hard coded the conditions in the conditional formatting. For example:
=AND($C6>=$D$10,$C6<$D$9)
I have done the same thing 5 times for each condition. This makes the formatting independent of the order of specification. I think it will work better across versions of excel.
To copy the same thing in all sheets, Shelly can copy these formatted cells with format painter and apply it to the relevant cells in next sheet and so on! I know 700 sheets will be difficult but I dont know of any other way to apply conditional formating rules to the whole sheet.
First i have used percentile formula in the next column of "percentile Threshold" where E5, E6.. is input to colour code.
The idea behind doing this is to replicate the formula for any range and any threshold
=PERCENTILE($C$3:$C$30,1-E5)
=PERCENTILE($C$3:$C$30,1-E6)
=PERCENTILE($C$3:$C$30,1-E7)
=PERCENTILE($C$3:$C$30,1-E8)
=PERCENTILE($C$3:$C$30,1-E9)
Now i have given logic to different employee by applying "if Formula"
=+IF(J3>=$G$5,1,IF(J3>=$G$6,2,IF(J3>=$G$7,3,IF(J3>=$G$8,4,5))))
where 'J" referes to PR and "G" refers to percentile derived from above mentioned formula.
once again it is replicable (just change reference points)
Now comes the major part of Conditional Formatting, i have used "use a formula to determine which cells to be formatted"
Formula =$j=5, format "required colour" Applies to "$I$3:$J$30"
plus put tick on stop if true
This solves the query, important point that this is repeatable and can be done for n number of departments
Thanks !
I had done some reading on it and in Excel 2010 a new function has been introduced, percentile.exc. Attaching a video which also talks why the old percentile function shouldn't be used as it acts erroneous at times. Might be worth a watch Chandoo,
http://www.itechtalk.com/thread10579.html
@Deepa
Quit correct.
Where ever you use statistical spreadsheet functions and are using excel 2010 you should use the new versions of the functions as MS did a lot of work to speed up and fix errors in the old functions.
Warning: If you use the new Excel 2010 statistical functions in Named Formulas most of them will crash excel so do keep that in mind.
Hello Chandoo,
When i first read the challenge file, i thought, the color that need to be applied for a given rule, also need to be picked dynamically as given in rule set. But in the solution file, i found that color is hard Coded. So in case, someone has same data, but wants different colors, he/she needs to goto manage rules and change colors.
Let me know if my understanding is correct, and if yes, can we also make the color to be applied dynamic?
Thanks
Kishore
HI I ALSO USED THE PERCENTILE FUNCTION. HOWEVER, I WENT A STEP FURTHER AND USING THE SMALL() FUNCTION I SORTED THE DATA BY PERCENTILE SO THE COLOSCHEME WOULD BE GROUPED BASED ON THE VALUE. THIS WAY IT IS BETTER AND EASIER TO VIEW.
[...] recently posted a challenge to help a reader with a [...]
Hi, i have got doubt regarding to the percentages that has been put in chandoo's spreadsheet, i cant understadn how he put directly. can some one please explain how chandoo put the percetages straight way that i stated below..
5%
15%
60%
10%
5%
I have stumbled on this post as the solution has been already given so I have taken the liberty to record a video where I show the implementation of it as well as adding a filtering feature which I hope can prove to be useful.
Thank you
http://www.xlninja.com/2012/06/28/how-to-use-excel-to-highlight-employee-performance-rating/
[...] scriu nici macar un cuvant din urmatorul articol. Astazi mi-am citit mailul si hopa challenge de la Chandoo. Cum puteam sa refuz asa ceva si m-am apucat de citit, iar dupa 5 min i-am spus sotului ca pe asta [...]
Question for Chandoo:
I came to your site late but am totally loving these challenges 🙂
I guess it all boils down to how the bins are set up.
I agree with the PERCENTILE.INC function.
pls help me understand where I am wrong.
I have determined following the bins:
bottom 5% <=2.00 (F6:F33 <=PERCENTILE(range,.05))
lower 15% (5+10) <= 2.40 (F6:F33 <=PERCENTILE(range,.15))
lower 80% (5+10+65) <=3.46 (F6:F33 <=PERCENTILE(range,.80))
lower 95% (5+10+65+15) <=4.00 (F6:F33 =PERCENTILE(range,.95))
top 5% <=4.20 (F6:F33 <=PERCENTILE(range,1.00))
I find that only Tom is highest scorer and unique top 5% achiever.
I notice that Chandoo has included Christy and Daniel in top 5% achievers. How can there be 3 people in top 5% out of a population of 28 (5% of 28 = 1.4, i.e. only one person can achieve that status)?
I tried different ways but cannot get to that distribution.
Rest of the work is simply organizing the conditional formatting rules with Stop If True box checked.
Thanks for your insights