fbpx
Search
Close this search box.

Employee Performance Panel Charts – Excel vs. R

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

15 Responses to “Employee Performance Panel Charts – Excel vs. R ”

  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.

      • Duncan Williamson says:

        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!

      • Jimmy says:

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

Leave a Reply