Today, Bill takes a look at final formatting. If you are going to publish your finished report to Excel Server or to SharePoint, you'll want it to look good! Episode #1341 offers you several tips for making the report look less like Excel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot for the Data Analyst, chapter 13.
Final Formatting.
Okay! So, now you've created your perfect power pivot model and you are going to publish this as a dashboard for other people to use.
Let's just try and make it look better than things would normally look in Excel.
Give you a few tips here, there's more in the book.
First thing slicers, they always show up as blue.
Let's change those, so choose a slicer, go to slicer tools options, choose just a different color for each one, it kind of adds a little bit of variety throughout.
So, that's our first thing.
Next, charts in Excel 10 to look horrible.
I mean, let's take a look at this one, title and legend of total are completely redundant.
We don't need that it, also click on the legend and press [ delete ], to get rid of the legend, allows the chart to be a little bit larger.
Triple click the title, you can type a new title there, sales by year.
You don't have to triple click on title but otherwise you're editing the title up here in the formula bar, instead of right there in the chart.
These little tags, some of revenue and year.
Those are great when you're building the chart, not so great afterwards.
So, we go up to the analyze tab, field buttons and say hide all.
That gets rid of all of those, a lot of zeros out here, those zeros are hard to read, I right-click, choose Format Axis, and then on the display units, choose the appropriate display units for example, millions in this case might be good.
Do we need those grid lines?
Are they really adding anything?
Probably not, click on the grid lines and press [ delete ] and the columns could stand to be wider.
So, if we right click, format data series.
Now you can't make the columns wider, boy! You can do is make the gap narrower.
So, here the gap with just make that smaller, you see that the columns become wider.
All right! A few more things. Yeah!
We use Excel all the time.
We're used to the grid line, the people in the boardroom are going to be looking at this dashboard.
They don't like the grid lines, go to the view tab and under view grid lines, we want to turn those grid lines off and just select a cell grid lines off.
Makes it look cleaner in fact, let's just add a color to the whole thing, select the whole spreadsheet, so I'm choosing a nice basic pastel color, back there.
What do you think? Let's go with that one.
If you want to have a title up in row 1, let's change the color of that title and we'll put xyz company, dashboard.
Give that a nice style.
I'll go cell styles and choose title.
All right! So, that becomes larger will zoom out here.
So, we can see the whole thing in one view and a kind of a nice little finishing touch, go down to a row below your data and add a matching swath of color down there.
All right! And then ideally if you're publishing this to Excel server, you're going to publish just this range basically from here right on up and I do have a great view of the data.
It won't look at all like Excel.
They'll be able to use the slicers and things will really look very, very impressive.
All right, hey! Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
PowerPivot for the Data Analyst, chapter 13.
Final Formatting.
Okay! So, now you've created your perfect power pivot model and you are going to publish this as a dashboard for other people to use.
Let's just try and make it look better than things would normally look in Excel.
Give you a few tips here, there's more in the book.
First thing slicers, they always show up as blue.
Let's change those, so choose a slicer, go to slicer tools options, choose just a different color for each one, it kind of adds a little bit of variety throughout.
So, that's our first thing.
Next, charts in Excel 10 to look horrible.
I mean, let's take a look at this one, title and legend of total are completely redundant.
We don't need that it, also click on the legend and press [ delete ], to get rid of the legend, allows the chart to be a little bit larger.
Triple click the title, you can type a new title there, sales by year.
You don't have to triple click on title but otherwise you're editing the title up here in the formula bar, instead of right there in the chart.
These little tags, some of revenue and year.
Those are great when you're building the chart, not so great afterwards.
So, we go up to the analyze tab, field buttons and say hide all.
That gets rid of all of those, a lot of zeros out here, those zeros are hard to read, I right-click, choose Format Axis, and then on the display units, choose the appropriate display units for example, millions in this case might be good.
Do we need those grid lines?
Are they really adding anything?
Probably not, click on the grid lines and press [ delete ] and the columns could stand to be wider.
So, if we right click, format data series.
Now you can't make the columns wider, boy! You can do is make the gap narrower.
So, here the gap with just make that smaller, you see that the columns become wider.
All right! A few more things. Yeah!
We use Excel all the time.
We're used to the grid line, the people in the boardroom are going to be looking at this dashboard.
They don't like the grid lines, go to the view tab and under view grid lines, we want to turn those grid lines off and just select a cell grid lines off.
Makes it look cleaner in fact, let's just add a color to the whole thing, select the whole spreadsheet, so I'm choosing a nice basic pastel color, back there.
What do you think? Let's go with that one.
If you want to have a title up in row 1, let's change the color of that title and we'll put xyz company, dashboard.
Give that a nice style.
I'll go cell styles and choose title.
All right! So, that becomes larger will zoom out here.
So, we can see the whole thing in one view and a kind of a nice little finishing touch, go down to a row below your data and add a matching swath of color down there.
All right! And then ideally if you're publishing this to Excel server, you're going to publish just this range basically from here right on up and I do have a great view of the data.
It won't look at all like Excel.
They'll be able to use the slicers and things will really look very, very impressive.
All right, hey! Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
