Full transcript
- Field Names Tip
0:00let's be honest excel's default pivot
0:02tables don't look great their number
0:05formatting color choice and header text
0:08it's rather questionable but if you
0:10follow these eight pivot table design
0:12tips you'll go from ugly pivot tables
0:15like this to something much more
0:17impressive like this so let's get into
0:20it starting with number one which is
0:22field names and let's first go over the
0:24data set that we'll be working with
0:26which is this one right here that you
0:28can download for free in the video
0:30description so let's first insert a
0:32pivot table by going to the insert Tab
0:35and just clicking on pivot tables we'll
0:38hit on okay there and here's my Pivot
0:40tables fields on the side which you can
0:42actually move around from here let's
0:44suppose that I want to have the products
0:46so I'm just going to go and select the
0:48products under my rows and then let's
0:50say that I want to have my Revenue my
0:53expenses and my profit under the values
0:57and you'll notice that for all of these
0:59we start to get the sum of sign at the
1:01very beginning on all three of them
1:03which is rather annoying now if we want
1:05to remove this you might think of just
1:07writing Revenue over it but as soon as
1:10we do you'll notice that it says that
1:12pivot table field name already exists
1:15that's because we already have it in
1:16here as Revenue so we're not going to be
1:18able to do that now a work around to get
1:21rid of that sum of across all of them in
1:24the table is simply to hit on controll H
1:28that's actually defined and replace
1:29Place pop up so what do we want to find
1:32just some of and we want to replace it
1:35with nothing and we'll hit replace all
1:38as we want to get rid of it for all of
1:40these so we'll hit replace all hit okay
1:44and hit close and now the reason we've
1:46been able to do that is because if we
1:48double click inside of it you'll notice
1:50that it's added an extra space so that
1:53makes it all work all right that's the
- Text & Column Format
1:55first STP done and now let's work on the
1:57second one which is the text and column
2:00format suppose we want to format these
2:02numbers in the table we might select
2:04them all with control shift down control
2:06shift right and just hit on control1
2:10that's going to show the formatting
2:11cells popup so we'll go to number here
2:14and let's say we want a separator for
2:16the commas and we don't want any decimal
2:19places so we'll hit on okay let's also
2:22say that we want to make these somewhat
2:24wider so we can see them a bit better
2:26and we also want to align this top part
2:28so all of these head we want to align
2:30them to the right so let's say that
2:32we're happy with this and now we import
2:35a few more rows of data so we would just
2:37go over to the pivot table analyze and
2:40hit on refresh in case we added more
2:42rows so just hit refresh there but
2:45you'll notice that it all goes back to
2:46the default format which is quite
2:48annoying as you had done all the
2:50formatting tips there now to prevent
2:52this from happening you just want to go
2:54inside of the pivot table right click
2:57and go to pivot table options towards
2:59the bottom botom from here at the very
3:01bottom you'll find that there's the
3:03autoit column wids which let's say that
3:05we don't want and there's also the
3:07preserve sell formatting which we do
3:09want that's going to keep the same
3:11number format and so forth so we'll hit
3:14on okay there let me fast forward how I
3:16do those changes
3:18again all right we're at the same spot
3:20as before and now this time when we hit
3:23on pivot table analyze and click on
3:25refresh you'll notice that it doesn't
3:27actually change the formatting which is
3:29we want it moving to number three and
- Layout Tips
3:32here we have some layout tips so over
3:35here you'll notice that I've rearranged
3:36the pivot table to add a few more values
3:39so you can see here what that layout
3:41looks like if you want to copy it feel
3:43free to pause this video so you'll
3:45notice that the table does look quite
3:47overwhelming as there's so much data so
3:50one quick tip here would be to just
3:52select anywhere inside the table under
3:54design we're going to go ahead and add
3:57some blank rows between these total
4:00so insert blank line after each item
4:03click on that now you can see we have a
4:05bit more breathing room then you might
4:08consider if you want to have these Grand
4:10totals on the side and on the bottom or
4:12not if you want to remove them you can
4:14just click inside Grand totals and just
4:17turn them off and again you can turn
4:19them back on fairly easily lastly here
4:23we have the subtotals which you can find
4:25at the top of each of these sections you
4:27can actually change this to the Bottom
4:29by going inside subtotals and say show
4:32subtotals at bottom of group sometimes
4:35this makes more sense and is easier to
4:38read in number four we've got removing
4:40the filters as of now you might have
4:43noticed we have some of these drop downs
4:45which are bit disrupting if we want to
4:47get rid of them we can just go to pivot
4:49table analyze and all the way to the
4:52side we can click on the plus and minus
4:55buttons as well as the field headers the
4:58plus and minus is going to be for these
4:59buttons right here like next to the
5:01Amazon or the Foot Locker so just
5:04deselect that and we'll exit out of that
5:06option and then the field headers is
5:08going to be that top part so we get rid
5:11of those two right there I can take them
5:13back on so you see where they are
5:16finally if you're showing the whole
5:17Excel file you might want to get rid of
5:19the fields list which is all of this
5:21data to manipulate so right now it just
5:24looks like a normal table and not a
5:26pivot table with the fields list there
5:28according to for
5:30almost 90% of Excel files have errors in
5:34them now if you don't want to fall into
5:36that category and get in trouble with
5:38your manager you can consider taking our
5:40Excel for business and finance course to
5:43learn all of the industry best practices
5:46impress your manager and avoid any
5:48errors with our comprehensive curriculum
5:51we cover everything you need to know
5:54ranging from formatting best practices
5:56and shortcuts to building awesome visual
5:59dashboard SPS creating large Dynamic
6:02Financial models and much more this is
6:05basically the course I wish I had before
6:07working in corporate jobs in business
6:10and finance so if you're interested
6:12check out the link in the description
6:14below and if you want more than just
6:16Excel we also offer several other
6:19courses including powerbi Finance
6:22evaluation and more all right back to
6:25the video in number five we've got
- Blank Cells
6:27adjusting blank cells so if we take a
6:30look at the pivot table that we're
6:31working on you notice that we have a lot
6:34of blanks now it's not very clear if
6:36this is a blank because there's an error
6:38or because it's simply a zero to find
6:41out we can just double click inside of
6:43them and notice here that we don't have
6:46any transactions which basically implies
6:48that there's no um values in there while
6:51in this one you'll see that we have a
6:53ton of data which basically means that
6:55it all looks correct but maybe it makes
6:57more sense to add a zero in there as
6:59opposed to leaving it blank so we can
7:02just go to right click and then under
7:05pivot table options towards the bottom
7:08here you'll find that we can either add
7:10something when there's an error but in
7:12this case we don't have any errors or
7:15for empty cells we can just show a zero
7:18there and hit on okay hopefully this
7:21makes things a bit more clear for our
7:23team number six let's work on making a
- Templates
7:26template for the pivot table Design This
7:29makes a a lot of sense for a company if
7:31you want to follow their color palette
7:33so over here the first thing that we
7:35want to do is under the design tab go to
7:38this drop down and just select a color
7:41that's similar to the one that U your
7:43company has let's say in my case it's
7:45this one right here I'm just going to
7:47click on that from there at the top once
7:50you have it selected go to right click
7:53and click on
7:55duplicate now this is the template that
7:58we'll use so let's say we call this one
8:00career principles which is the name of
8:03my company and we want to make certain
8:06changes for example let's suppose that
8:08the Amazon total for Locker total and so
8:11forth we want in a yellow color we will
8:13go ahead and find the sub total row
8:16which is this one right here and go to
8:19format under filler is where we want to
8:22change things pattern color and I'm just
8:25going to go for a recent yellow color
8:27that I already had there and now under
8:29the font we also want to make sure that
8:31this color is black instead of white
8:34there hit on okay and okay again now if
8:38we go inside you'll notice there's no
8:40changes but once we go to the drop down
8:43we now have the custom up top which is
8:45our template if we click on it you'll
8:47see here that we start to see how it's
8:49changed with that yellow header so
8:52there's really a lot of customization
8:53that can be done here in number seven we
- Adding Dates Tips
8:56have adding dates and there's a few ways
8:58to go about this but in a scenario like
9:01this one where we already have a lot of
9:03data if we go ahead and add the dates
9:06like on inside of the rows you'll notice
9:08that it just gets too messy as there's
9:10too much information let's get rid of
9:12that for now I'm just going to drag drag
9:14that out so all of the date fields I'm
9:17dragging out there and instead a better
9:19way is to actually go to the pivot table
9:22UNL Tab and just click on insert a
9:25timeline click on the date there and hit
9:28on okay now you'll see that we have this
9:31timeline to the side which is fully
9:33Dynamic we can change the months like so
9:36we can even change this into a quarter
9:39on a quarterly basis like so and even by
9:43year another way that we could have done
9:45it instead of adding it in the rows
9:47there is just selecting the dates and
9:49putting it as a filter up top the
9:52problem is this one's not as easy to
9:54spot so for someone that doesn't really
9:56know how to use things the timeline
9:58probably makes more sense finally in
10:01number eight we have adding visual
- Visual Support
10:03support and this makes the most sense
10:05for comparison purposes for example in a
10:09table like this one over here where we
10:11have the column under the region the
10:14values as a profit and the row as the
10:16retailer it's difficult to see exactly
10:19which number is high and which one is
10:21low so one way to go about this is just
10:23select the inside area so this part
10:26right there and go to conditional
10:28formatting under data bars because this
10:31is profit let's say we go for green as
10:34that's positive generally and we can see
10:36there that the and inside of the West
10:38the West gear is really the one that's
10:40standing out as the best retailer in
10:43that western region and the best overall
10:45for us awesome now before you leave
10:48there is one final bonus feature that
10:50you probably didn't know let's suppose
10:52that we want to send this to our manager
10:54so instead of just copying and pasting
- Bonus Feature!
10:57it which does come with some
10:58difficulties there's actually a better
11:00way which you'll see why now so we can
11:03go over to this camera icon if you don't
11:06find it just go inside of this dropdown
11:09under more
11:11commands under popular commands up over
11:14here you want to click on all commands
11:17and just search for the camera so it's
11:19going to be camera just type it in there
11:21once you find it just click on ADD and
11:24click on okay in my case I don't need to
11:27as I already have it but but now all you
11:30want to do is select the area you're
11:32interested in which is going to be this
11:34entire table just click on the camera
11:37icon now if we click anywhere else we've
11:40basically pasted that but what's nice
11:43here is that we can easily resize it
11:45because it works just like an image now
11:48you might wonder what if the data
11:50changes well if we change the label here
11:52to Southwest for example just for us to
11:55see what happens you'll notice that that
11:57visual also updates Auto automatically
12:00so it's a fully Dynamic image that can
12:02be resized to fit your presentation now
12:05that you understand how to make pivot
12:07tables look nicer check out this video
12:09to make beautiful Excel charts or take
12:12our Excel course over here hit the like
12:15and that subscribe and I'll catch you in
12:17the next one