Tip:
Highlight text to annotate it
X
today we're gonna go through
an excel file
and create a summary for
a set of data with
number of days here in column A
then we're going to create a histogram
from that data
and we're also going to create a pareto chart
we're going to have different buckets and counts
of number of days
so one of the first things we do
is to highlight
the raw data
then go out to insert on the menu bar
create a pivot table
column A is already put in here by excel automatically
and we just accept this so we hit ok
the pivot table designer shows
and you can see the days here we will
drag-and-drop of days down to the row labels
also drag-and-drop days down to
the values
since it says sum we need to change that to count
using dropdown on value field settings
now you see a count rather than
a total
now at this point we're still seeing all of the
raw data represented and we need to have
some real realistic buckets
so what we do is we right click on any one of the cells that is showing
the number of days
click on the option for group
you see a popup
goes from the beginning minimum number to the ending maximum number
we want to
go a little bit lower than the minimum
and a little bit higher than the maximum
and change the incremental amount to twenty
now we have the
summary level
set of data
what i do to create the graphics
let's go ahead and copy the
data that is created
by right-clicking and copy
to somewhere out here to the right
then paste the values
we can actually create the
histogram now based upon this data we select it all
a histogram has no gaps
what we want to do is to highlight
any of the bars
if you click once a left click
it will highlight all of them and you'll see the surrounding little
circles around the bars
next is right click
choose format data series
you see here where it reads width
we want to slide that over to zero so there is no gap
this is the histogram
since we have the title
we don't really need
this legend so i highlighted the legend and hit the delete key on the keyboard
to create a pareto chart
it's basically sorted high to low column chart
let's go ahead and make a copy of the data so we do not mess up the
chart when we modify the data
what we're gonna do now is sort
by going up to the data menu bar and
hitting sort
choosing count
in this case largest to smallest
hit ok
you now see data sorted large to small
select the data to make a chart
we just go back now to insert menu bar
to the column chart option
this is now a pareto chart
because it's now sorted by count high to low
we can get rid of the count and make it look like this
so at this point we have both
the histogram
and the pareto charts