Employee Performance Panel Charts – Excel vs. R [video]

Posted on August 10th, 2017 in Charts and Graphs , R programming - 13 comments

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.

panel-charts-excel-vs-r

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:

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.

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

13 Responses to “Employee Performance Panel Charts – Excel vs. R [video]”

  1. kidakaka says:

    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?

    • Jimmy says:

      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.

  2. Jordan Goldmeier says:

    I love me some R. If you know R and Excel in life, you're golden. (Also markdown.)

  3. Jordan Goldmeier says:

    In this case though you'd want to be using ggplot

  4. GraH says:

    Could you create the same script in PowerBI? There is a button for R-script. Don't have a clue how that works.

  5. #Excel Super Links #124 – shared by David Hager | Excel For You says:

    […] http://chandoo.org/wp/2017/08/10/panel-charts-excel-r/ […]

  6. NARAYAN says:

    Hi ,

    A fun and painless way to learn R :

    http://tryr.codeschool.com/

  7. Jimmy says:

    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!

  8. GJ says:

    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?

  9. Xiq says:

    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.

  10. Pradeep Ankem says:

    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

    • Chandoo says:

      @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.

Leave a Reply