in the previous video we demonstrated
how to make a basic pie chart in Excel
and talked about how they're useful but
what a pie chart can't easily do is give
you information on how the overall
dataset compares to another dataset one
way to get around this is to create
special comparative pie charts where the
total size of the pie is proportional to
the sum of all the data it is showing
allowing you to see comparisons between
data sets and the categories within them
at the same time to start with we'll
just compare two small sets of data from
different years I already have two
generic pie charts showing the data see
our previous video on pie charts to see
how to get to this stage note that for
comparative pie charts we recommend that
you do not use a legend and instead have
category names appear on the pie itself
in the form of data labels which we also
explained in our previous videos now the
objective is to make the size of the pie
in the second chart represent the total
for its data set relative to the first
this is what is known as a comparative
pie chart and unfortunately it isn't
something Excel can do by default so to
achieve this goal
we'll use a bit of custom computer code
using Excel's VBA programming language
also known as a macro that may seem
daunting but we've already written the
code for you you'll be able to find in
the description to this video located
underneath the player on the YouTube
watch page and in the downloadable
example spreadsheet linked in that
description but first we need to do a
few preparations first I need to give
names to the two totals calling the
first total 2013 and the second total
2014 now I give names to my two charts
calling the first chart 2013 and the
second chart chart 2014 note that all
these names don't have spaces in them
specifically because it's easier to work
with names that are all one word when it
comes to VBA note that the regular
naming method doesn't always work for
charts in which case you can use the
name box in the Layout tab of the ribbon
instead with the name set up I'm ready to
add in my code press Alt + f11 to bring
up the VBA window from the insert menu
at the top click on module this gives us
a new space in which to put
VBA code now I simply paste the code
into this space so what does this code
do well in simple terms the first part
establishes all the names we've chosen
the second part stores how big the first
chart is in the computer's memory
through temporary variables the third
part selects the second chart and makes
it the same size the first to start with
and the final part scales up the second
chart according to the ratio of the
totals in each data set if you want to
give your totals or charts different
names you'll need to change the names
being used in the code too by replacing
the definitions of the named variables
at the top now the code is in the
spreadsheet I just hit the play button
at the top of the screen while the
cursor is somewhere in my code and Excel
will execute it back in the spreadsheet
we can now see the second chart has been
scaled up to reflect the higher 2014
total giving us more information through
the chart comparison than was possible
originally if I change the data and need
to run the macro again I can either
press Alt + f11 to get the code window
open again and reuse the play button
method or I can see the macros in my
spreadsheet through the macros button in
the View tab I select the name of the
macro I want and click run to execute it
so with this I can resize my pie
whenever the data sets change overall I
now have two pie charts that I can
present separately from the total sales
data that not only tell me the split of
sales in the year but the total
performance as well overcoming one of
the major downsides of using pie charts
for comparisons I hope you found this
tutorial useful check the links in the
description and on screen to reach the
excel tips page on our website with more
tricks to try and a downloadable version
of the file I've been showing you with
the VBA macro already set up be sure to
like and comment the video if it helped
and subscribe to our channel for more
access Excel and VBA tips thanks for
watching
 
