If you are going to publish your PowerPivot report to Excel Server or to SharePoint, you want it to look good. This video gives 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!
OK, so now you've created your perfect PowerPivot 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 tend to look horrible, let's take a look at this one.
The title and legend of total are completely redundant, we don't need that at all.
So click on the legend, and press Delete to get rid of the legend, allows the chart to be a little bit larger.
“Total”, If you triple-click the title, you can type a new title there, “Sales by Year.
You don't have to triple-click in title, but otherwise you're editing the title up here in the formula bar, instead of right there in the chart.
These little tags, Sum 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?
The columns could stand to be wider, so we right-click, Format Data Series, now you can't take the columns wider, but what you can do is make the gap narrower.
So here, the gap, let’s just make that smaller, you see that the columns become wider.
Alright, a few more things.
Yeah, we use Excel all the time, we're used to the gridline, the people in the boardroom we're going to be looking at this dashboard, they don't like the gridlines.
Go to the View tab, we want to turn those gridlines off, so just select a cell, Gridlines Off, makes it look cleaner.
In fact, let's just add a color to the whole thing, select the whole spreadsheet, choose 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”.
Yeah, give that a nice style.
I'll go Cell Styles and choose Title, alright, so that becomes larger, we'll zoom out here so we can see the whole thing in one view.
And kind of a nice little finishing touch, go down to a row below your data, and add a matching swath(?) of color down there, alright.
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 they'll 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.
Alright hey, I 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!
OK, so now you've created your perfect PowerPivot 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 tend to look horrible, let's take a look at this one.
The title and legend of total are completely redundant, we don't need that at all.
So click on the legend, and press Delete to get rid of the legend, allows the chart to be a little bit larger.
“Total”, If you triple-click the title, you can type a new title there, “Sales by Year.
You don't have to triple-click in title, but otherwise you're editing the title up here in the formula bar, instead of right there in the chart.
These little tags, Sum 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?
The columns could stand to be wider, so we right-click, Format Data Series, now you can't take the columns wider, but what you can do is make the gap narrower.
So here, the gap, let’s just make that smaller, you see that the columns become wider.
Alright, a few more things.
Yeah, we use Excel all the time, we're used to the gridline, the people in the boardroom we're going to be looking at this dashboard, they don't like the gridlines.
Go to the View tab, we want to turn those gridlines off, so just select a cell, Gridlines Off, makes it look cleaner.
In fact, let's just add a color to the whole thing, select the whole spreadsheet, choose 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”.
Yeah, give that a nice style.
I'll go Cell Styles and choose Title, alright, so that becomes larger, we'll zoom out here so we can see the whole thing in one view.
And kind of a nice little finishing touch, go down to a row below your data, and add a matching swath(?) of color down there, alright.
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 they'll 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.
Alright hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
