Due to time constraints, Tyler needs to set up a Chart and a Table and she would like to do both simultaneously. Can it be done? Of course! It's Microsoft Excel! Follow along with Episode #1625 as Bill "MrExcel" Jelen shows us how it's done with a little available 'control' in how the final product is produced.
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
...Also, for more on Charting and Graphs in Microsoft Excel, see the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel. Charts and Graphs: Microsoft Excel 2010
"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
...Also, for more on Charting and Graphs in Microsoft Excel, see the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel. Charts and Graphs: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don't Fear The Spreadsheet podcast episode number: 16-Chart And Table.
Well, hey, welcome back to the don't fear the spreadsheet Podcast.
I Bill Jelen from MrExcel with today's question.
It's Tyler Nash.
"I'm like super, super pressed for time.
Is there any way that I can set up a chart and a table simultaneously or no?" Chart and Table simultaneously...
I just wish the answer was no.
The answer is yes, yes, you can but I hate the options.
Okay, so here's our chart.
We come back here to the layout tab.
You have to get, you have to click the chart to get to the Layout tab.
So, if you click outside the chart, no Layout Tab.
Click inside the Chart Layout tab is back.
Data table, we have none. Yes.
Show Data Table, Show Data Table Legend Keys, let's compare.
Show Data table has a little data table down there.
Data Table> Show Data Table with legend keys, which would allow us to get rid of the legend, which would make a little bit more space I guess.
Although now this breaks the rule because now the legend isn't the bottom instead of at the top.
Here's why I don't like data tables.
Boy that seems really really busy down there.
I'd like to do a little bit of formatting to it and let's see what we can do.
So, I go click on the Data Table and Format Data Table.
Change the Fill, you change the border color.
I'm gonna add shadow.
There's nothing in here lets me change the number Format.
I can turn the borders on or off?
I just don't feel like I have a lot of control here and if I want to change the number format, I'd have to come back here and change the number format.
So, for example, I'm going to use [ CTRL+1 ] to get to format cells.
That's the same as clicking this little dialogue or a little dialog launcher.
[ CTRL+1 ] go to the number tab.
Thousand separator, zero decimal points and I even want to do the little trick to put it in thousands.
So, comma.
The comma puts it in thousands of K.
Adds a little K afterwards.
We see then that screws up.
My legend here.
I have to come back here and change the legend and it just seems like I don't have a lot of control And if you know me, I like to have control.
So, the data table, it's really easy to do.
I mean this could have been a three-second podcast.
Yeah! Tyler click here.
But it's just it frustrates me to knowing that I don't seem to have the control.
that I want with the data table.
So, a couple of options.
One, choose the series Layout> Data labels> Outside End and then do some formatting.
So, you know that way you kind of have the data table right there on top of the chart.
That's option number one.
I'm going to go into the next worksheet option number two is, Tyler you already had a data table in order to create the chart, So, what's the need to use that horrible looking data sheet, when you can already format this one.
However you like.
So, you know here I used cell styles and my favorite heading 4.
I want in to have more borders.
So, that's under Borders> More Borders, and I could change the color and exactly whichever borders I wanted.
If there was one cell that I wanted to highlight like right here July is way up something good happen there in July and I can add a little bit of you know some sort of cell formatting to that.
And then if I want that table to appear below the chart, just insert a bunch of rows here.
I was about to use [ ALT+I+R ].
Sorry, Insert sheet.
Insert and then drag the chart up to fit that spot.
Didn't quite work few more rows.
[ ALT+I+R ] Old habits are hard to break.
All right set the prior Range.
Put to one page wide.
All right, so now I actually have a chart and a table that I can absolutely control.
So, well it took a little bit more time than just going to Layout> Data table.
And you know choosing one of there a couple of options there.
You have so much more control using this method.
Well, hey, I want to thank everyone for stopping by and check out the book Don't Fear The Spreadsheet.
This book makes Excel For Dummies looks like it was written for rocket scientists.
Don't Fear The Spreadsheet podcast episode number: 16-Chart And Table.
Well, hey, welcome back to the don't fear the spreadsheet Podcast.
I Bill Jelen from MrExcel with today's question.
It's Tyler Nash.
"I'm like super, super pressed for time.
Is there any way that I can set up a chart and a table simultaneously or no?" Chart and Table simultaneously...
I just wish the answer was no.
The answer is yes, yes, you can but I hate the options.
Okay, so here's our chart.
We come back here to the layout tab.
You have to get, you have to click the chart to get to the Layout tab.
So, if you click outside the chart, no Layout Tab.
Click inside the Chart Layout tab is back.
Data table, we have none. Yes.
Show Data Table, Show Data Table Legend Keys, let's compare.
Show Data table has a little data table down there.
Data Table> Show Data Table with legend keys, which would allow us to get rid of the legend, which would make a little bit more space I guess.
Although now this breaks the rule because now the legend isn't the bottom instead of at the top.
Here's why I don't like data tables.
Boy that seems really really busy down there.
I'd like to do a little bit of formatting to it and let's see what we can do.
So, I go click on the Data Table and Format Data Table.
Change the Fill, you change the border color.
I'm gonna add shadow.
There's nothing in here lets me change the number Format.
I can turn the borders on or off?
I just don't feel like I have a lot of control here and if I want to change the number format, I'd have to come back here and change the number format.
So, for example, I'm going to use [ CTRL+1 ] to get to format cells.
That's the same as clicking this little dialogue or a little dialog launcher.
[ CTRL+1 ] go to the number tab.
Thousand separator, zero decimal points and I even want to do the little trick to put it in thousands.
So, comma.
The comma puts it in thousands of K.
Adds a little K afterwards.
We see then that screws up.
My legend here.
I have to come back here and change the legend and it just seems like I don't have a lot of control And if you know me, I like to have control.
So, the data table, it's really easy to do.
I mean this could have been a three-second podcast.
Yeah! Tyler click here.
But it's just it frustrates me to knowing that I don't seem to have the control.
that I want with the data table.
So, a couple of options.
One, choose the series Layout> Data labels> Outside End and then do some formatting.
So, you know that way you kind of have the data table right there on top of the chart.
That's option number one.
I'm going to go into the next worksheet option number two is, Tyler you already had a data table in order to create the chart, So, what's the need to use that horrible looking data sheet, when you can already format this one.
However you like.
So, you know here I used cell styles and my favorite heading 4.
I want in to have more borders.
So, that's under Borders> More Borders, and I could change the color and exactly whichever borders I wanted.
If there was one cell that I wanted to highlight like right here July is way up something good happen there in July and I can add a little bit of you know some sort of cell formatting to that.
And then if I want that table to appear below the chart, just insert a bunch of rows here.
I was about to use [ ALT+I+R ].
Sorry, Insert sheet.
Insert and then drag the chart up to fit that spot.
Didn't quite work few more rows.
[ ALT+I+R ] Old habits are hard to break.
All right set the prior Range.
Put to one page wide.
All right, so now I actually have a chart and a table that I can absolutely control.
So, well it took a little bit more time than just going to Layout> Data table.
And you know choosing one of there a couple of options there.
You have so much more control using this method.
Well, hey, I want to thank everyone for stopping by and check out the book Don't Fear The Spreadsheet.
This book makes Excel For Dummies looks like it was written for rocket scientists.
