Today, Tyler is preparing for a presentation and rather than presenting her Data as strings of hard to read numbers and text she would like to create a Chart. If you've never created a Chart with Microsoft Excel then today is your lucky day! Today in Episode #1608 Bill shows us how to quickly create a Chart from our Data Set!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Bill Jelen: MrExcel podcast is sponsored by: Easy-XL.
Don’t Fear The Spreadsheet Podcast, Episode 15 -- Change Excel Data into A Chart.
Hey, welcome back to the Don’t Fear the Spreadsheet Podcast.
I'm Bill Jelen from MrExcel.com -- today's question, here's Tyler Nash.
Tyler Nash: I have to do a presentation today and the people who I'm giving their presentation to clearly do not want to have to read all of the data in my spreadsheet.
So, is there any way that I can convert that into a chart?
Bill Jelen: Hey, all right Tyler.
Okay now, you know -- Don’t Fear the Spreadsheet is a book for people who are brand new to Excel and there is the world's shortest chapter in there about creating charts.
It has two chart examples; enough to get you started, but you can really write a whole book about charts and I have written a whole book about charts.
It's available from QUE in the MrExcel library called: Charts and Graphs Microsoft Excel 2010.
Graphs, that’s the word that scientists use while accountants tend to use charts -- so to create a chart in Excel it's very easy to get a chart, maybe not the right chart but you can get a chart pretty quickly.
I have data here, here's all my numbers; headings along the left side, headings across the top.
Top left cell, I usually use that blank -- it's important to leave it blank if there's numbers across the top or dates across the top; now these don't count as dates, but just it's a good habit to leave that top left corner blank.
So, you come back here to the insert tab and they have a whole bunch of different charts: pie charts, bar charts, column charts.
Usually for my rule is for time series, we're showing time going across it's best to use a column chart for up to 12 points.
So we have 12 months here, that'll work well -- beyond that, I might go to a line chart like if I had, 52 weeks or something like that because the columns get to do narrow.
So column, even within the column look at how many different types they have here -- there's 19 different types but it's really not that intimidating.
For most chart types there's what they call the clustered; so this would be your last year and this year next to each other -- and then stacked that would stack the two, which wouldn't make sense in this case.
And then the 100%, this is to show the share, percentage share or something and that's really not what we have here.
These older types pyramid, cone, and cylinder -- they may look cool but here's a simple rule, never use them, they're bad charts.
They've left them in because some legacy spreadsheet, a long time ago put them there; they misrepresent the data, just don't ever use them.
Yeah I hear people say the 3D charts look cool but again, if you want to create good professional looking chart, to stick with the 2D chart.
So, click right there -- now we have three chart tabs: design, layout, and format.
On the Design tab they have this drop-down which I'm not a big fan of, because it makes you think there's only 11 ways to show this chart, and there's not, there's 50,000 ways to show this chart so I usually skip this.
I can kind of look at some colors here, you know I had a boss once who said – hey never, never use red to represent revenue; red is bad he said, never use red.
So you know, we could change it to some sort of a green chart -- you know, for me though, most of the time I go straight to Layout or I can use these settings here, these dropdowns.
The legend, I always put that at the top -- Edward Tufte says the legend, you should encounter the legend before you encounter the chart so you either put the legend on the left or the top, I always go to the top.
The chart should have a good title, so about the chart I'm going to click-to-select and say that this is "OurCo Sales Year over Year".
All right so there's the chart title, make the chart a little bit larger -- if you don't like the colors that were available back here in the design tab, you actually have a lot of control.
So I'll come to the format tab and I will choose last year's series and this year's series, let's do this year first -- we'll choose a nice green and then we'll choose last year and we'll try and make last year a little bit less prominent so it's just kind of there as a reference, so a lighter green.
If we want to make the columns wider, there is no setting for making the columns wider, but there is a setting for making the gap narrower.
So I'll right click Format Data Series and make the gap narrower -- that just makes the gap, the columns wider, more prominent.
Again lots of zeros out here, if you don't like these zeros back at the Layout tab, Axes -- this is the vertical axis and Show Axis in thousands.
All right, so that's a little bit cleaner.
And people say the gridlines, you know, some people say don't put gridlines in at all – I’ll leave them there but I make them less prominent.
So on the Format tab, Shape Outline, and we go to gray, and even change the weight of them to real small so they're kind of there, but they're not overwhelming.
All right so that's kind of the quick, the quick answer.
Now, there's literally, there's 50,000 different ways to change the data in this chart, but that gets you started.
Tyler Nash: Thanks for stopping by, we'll see you next episode.
Check out Don’t Fear the Spreadsheet -- this book makes Excel for Dummies look like it was written for rocket scientists.
Don’t Fear The Spreadsheet Podcast, Episode 15 -- Change Excel Data into A Chart.
Hey, welcome back to the Don’t Fear the Spreadsheet Podcast.
I'm Bill Jelen from MrExcel.com -- today's question, here's Tyler Nash.
Tyler Nash: I have to do a presentation today and the people who I'm giving their presentation to clearly do not want to have to read all of the data in my spreadsheet.
So, is there any way that I can convert that into a chart?
Bill Jelen: Hey, all right Tyler.
Okay now, you know -- Don’t Fear the Spreadsheet is a book for people who are brand new to Excel and there is the world's shortest chapter in there about creating charts.
It has two chart examples; enough to get you started, but you can really write a whole book about charts and I have written a whole book about charts.
It's available from QUE in the MrExcel library called: Charts and Graphs Microsoft Excel 2010.
Graphs, that’s the word that scientists use while accountants tend to use charts -- so to create a chart in Excel it's very easy to get a chart, maybe not the right chart but you can get a chart pretty quickly.
I have data here, here's all my numbers; headings along the left side, headings across the top.
Top left cell, I usually use that blank -- it's important to leave it blank if there's numbers across the top or dates across the top; now these don't count as dates, but just it's a good habit to leave that top left corner blank.
So, you come back here to the insert tab and they have a whole bunch of different charts: pie charts, bar charts, column charts.
Usually for my rule is for time series, we're showing time going across it's best to use a column chart for up to 12 points.
So we have 12 months here, that'll work well -- beyond that, I might go to a line chart like if I had, 52 weeks or something like that because the columns get to do narrow.
So column, even within the column look at how many different types they have here -- there's 19 different types but it's really not that intimidating.
For most chart types there's what they call the clustered; so this would be your last year and this year next to each other -- and then stacked that would stack the two, which wouldn't make sense in this case.
And then the 100%, this is to show the share, percentage share or something and that's really not what we have here.
These older types pyramid, cone, and cylinder -- they may look cool but here's a simple rule, never use them, they're bad charts.
They've left them in because some legacy spreadsheet, a long time ago put them there; they misrepresent the data, just don't ever use them.
Yeah I hear people say the 3D charts look cool but again, if you want to create good professional looking chart, to stick with the 2D chart.
So, click right there -- now we have three chart tabs: design, layout, and format.
On the Design tab they have this drop-down which I'm not a big fan of, because it makes you think there's only 11 ways to show this chart, and there's not, there's 50,000 ways to show this chart so I usually skip this.
I can kind of look at some colors here, you know I had a boss once who said – hey never, never use red to represent revenue; red is bad he said, never use red.
So you know, we could change it to some sort of a green chart -- you know, for me though, most of the time I go straight to Layout or I can use these settings here, these dropdowns.
The legend, I always put that at the top -- Edward Tufte says the legend, you should encounter the legend before you encounter the chart so you either put the legend on the left or the top, I always go to the top.
The chart should have a good title, so about the chart I'm going to click-to-select and say that this is "OurCo Sales Year over Year".
All right so there's the chart title, make the chart a little bit larger -- if you don't like the colors that were available back here in the design tab, you actually have a lot of control.
So I'll come to the format tab and I will choose last year's series and this year's series, let's do this year first -- we'll choose a nice green and then we'll choose last year and we'll try and make last year a little bit less prominent so it's just kind of there as a reference, so a lighter green.
If we want to make the columns wider, there is no setting for making the columns wider, but there is a setting for making the gap narrower.
So I'll right click Format Data Series and make the gap narrower -- that just makes the gap, the columns wider, more prominent.
Again lots of zeros out here, if you don't like these zeros back at the Layout tab, Axes -- this is the vertical axis and Show Axis in thousands.
All right, so that's a little bit cleaner.
And people say the gridlines, you know, some people say don't put gridlines in at all – I’ll leave them there but I make them less prominent.
So on the Format tab, Shape Outline, and we go to gray, and even change the weight of them to real small so they're kind of there, but they're not overwhelming.
All right so that's kind of the quick, the quick answer.
Now, there's literally, there's 50,000 different ways to change the data in this chart, but that gets you started.
Tyler Nash: Thanks for stopping by, we'll see you next episode.
Check out Don’t Fear the Spreadsheet -- this book makes Excel for Dummies look like it was written for rocket scientists.
