Recently, I had to make a bunch of panel charts. After wrangling with Excel (and a tiny bit of VBA) to create them, I wondered if we are suffering needlessly by being too loyal to Excel. I switched to R and could create these panel charts in almost no time (well, first I had to learn how to pivot the data using dplyr). Today, let me share the experience.
The scenario – Visualize employee performance ratings
Around July, most companies in NZ have their employee performance reviews (or rem reviews). In August, all this review data is, well, reviewed so managers can decide whether any moderation (curve fitting) is needed and how much hike to offer. To help with this process, you need to first understand what proportion of staff are in each rating category. If you are head of a large company, then naturally, you want to know how this spread looks like at overall level as well as individual business group, branch levels, ie at various hierarchies.
The solution – Panel Charts
One quick and easy way to do this is by making lots of charts.
Enter Panel Charts.
Let’s say you have 80 branches, spread across 10 business groups. Each branch has different number of people. To understand if the ratings are normally spread in a branch, you can make a column chart showing number of people in each rating category. Then to see how this spread looks across the company, simply create 80 charts, one per branch.
Related: Introduction to Excel Panel Charts.
80 Charts – that is not fun!!!
Tell me about it. You might need a strong cup of espresso and right mood for lots of copy pasting.
Or you could use some VBA to do the dirty work for you.
In either case, you are going to waste (and hate) that afternoon.
An alternative – use Sparklines
If you are not in the mood for manic copy pasting, then you could use sparklines to cheat. Once you make 80 sparklines, you can just move them around so they look like an 8×10 grid.
It is still a lot of work, but less annoying than creating 80 charts.
A better solution – use R
If you are familiar with programming (VBA/C or something else) you can quickly learn the basics of R and use simple code to convert raw data to elegant panel chart in almost no time.
OK, show me how.
Watch below video. I explain both Excel sparkline and R approaches. As the process is elaborate (especially the Excel one), I am using video format. See it below or on Chandoo.org channel.
Download the resources – Excel file, R code and CSV
Use below links to download files:
- R code panel-gen.r
- Excel workbook with sparkline panel charts – panel-charts
- Raw data in CSV format – rem-data
Have you used R yet?
R is fun, interesting and challenging. If you have some free time, pick it up. It is free and offers heaps of possibilities for statistical analysis, data science, modeling, visualization, data cleanup and exploration. If you are looking for a book, I suggest R for Data Science by Hadley Wickham and Garret Grolemund.
If you are doing some R programming, please share your experiences and thoughts in comments.
15 Responses to “Employee Performance Panel Charts – Excel vs. R ”
What's this!! From Excel to R ... most interesting shift. Chandoo, when would you take a task up in Excel, when in VBA and when in R?
The better I get with R, the less I use Excel and VBA. I use R for the heavy lifting and throw the results into Excel to share with colleagues. As I get better with rmarkdown, I'll put the results into a word document or a PDF file to share with others.
I love me some R. If you know R and Excel in life, you're golden. (Also markdown.)
In this case though you'd want to be using ggplot
Could you create the same script in PowerBI? There is a button for R-script. Don't have a clue how that works.
[…] http://chandoo.org/wp/2017/08/10/panel-charts-excel-r/ […]
Hi ,
A fun and painless way to learn R :
http://tryr.codeschool.com/
After learning tons of Excel on this site, and migrating to Power Pivot and Power Query for larger spreadsheets, Excel got a little slow for me. So, I started learning R. These days, I still use Excel, but I do all of the heavy lifting in R. For data munging and combining and analyzing data from disparate sources, R is faster and kinda fun.
If you're looking to learn R, install R , RStudio, then the tidyverse package. Learn R the tidyverse way!
As someone who's comfortable with VBA, I'm sometimes at a fork between starting to learn Python or R. I personally prefer python, but is there a specific reason Excel nerds would favour R over Python?
Hi ,
You wouldn't get a conclusive answer ; what you will get are opinions.
See this thread , and a lot of other related threads :
https://www.quora.com/Why-is-the-R-programming-language-still-used-if-there-are-better-languages-like-Python-for-data-analysis
Personally I'm using a lot(!) of KNIME nowadays. It's many drag-and-drop functions in your work area and you can enhance it by using code/scripting nodes. There are code/scripting nodes for various different coding languages, including JAVA, Python, R and Perl.
The only downside of using KNIME, is that I no longer use Excel 🙁
Would love to see more "Excel vs [other program]" comparisons.
To be frank, you are very late in Posting stuff about R & Data Science.
If you have an artistic bent, you better prefer both.
If you are Pure Analyst, who can imagine things all in his mind...R should suffice.
Here is my recent post, you might want to have a look : https://medium.com/@lookdeepu/data-gamblers-toolkit-94ea537f59ff
@Pradeep... Better late than never. I disagree that R alone is sufficient. It is a clunky software for most ad-hoc analysis. Excel is easier when collecting, analyzing and visualizing many real life datasets. R shines when it comes to creating statistical and data science models and making stunning visualizations from them. As an analyst, you are better off learning both than either one.
You are right Chandoo but with a couple of other things to appreciate.
R knows what to do with data almost instinctively: a vector is a vector, a data frame is a data frame. 99.99999% of my stuff in R comes from a CSV file so there's my Excel link but R understands my ranges/tables
Have you checked how many packages there are for R? I checked about 9 months ago ... there were over 12,000 then. That's tiny, small, medium and large sized add ins that Excel does not really have. Of course, some packages are exceptionally specific and they can be very picky. However, you want descriptive statistics automatically? You want those fantastic graphics you mentioned? I think you know Benford's law: I use and teach Benford's Law and I know the effort it takes in Excel but I use the two or three Benford packages in R and I have done my work in just a few minutes.
I am currently creating an analysis of Blood Pressure with a small but growing set of data: I am using Excel to store the data, create descriptive stats, draw a series of graphs ... all basic and good looking stuff. I turned that into a CSV file and now I have got R to do my analysis: stats, scatter graphs, histograms, boxplots, include and exclude certain columns, panel charts ... markdown
One other thing about R is that it is easy to play around with it. As you demonstrated with the panel charts... there are some things you just don't want to do in Excel that R just does!
I think it's great that you are sharing your R experiences!
@Chandoo R is sufficient. With rmarkdown, flexdashboards, and shiny to name a few, one can do amazing things!
What can you do with Excel that you cannot do with R?