Locked lesson.

Upgrade

About this lesson

Learn how to create and manage Custom Views in a worksheet.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Custom Views.xlsx
22.6 KB Custom Views - Completed.xlsx
23 KB

Quick reference

Custom Views

Creating and managing Custom Views on a worksheet.

When to use

Custom views can be used to build a pre-packaged view of our spreadsheet, including hiding columns or rows. This can be very useful to build different looks to print out or show on screen.

Instructions

Creating Custom Views

  • Expand the outlining to level 3
  • Go to the View tab > Custom Views > Add
  • Give this View the name of “Full” and click OK
  • Collapse the outlining to level 2
  • Go to the View tab > Custom Views > Add
  • Give this View the name of “Compressed” and click OK

Using Custom Views

  • Go to the View tab > Custom Views > Select the view you want > Click Show
  • The view will be activated

Modifying Custom Views

  • Show the Compressed view
  • Click the – button to collapse line 11 (the Revenues section)
  • Go to the View tab > Custom Views > Add
  • Give this View the name of “Compressed” and click OK
  • Click Yes when you are asked if you’d like to overwrite the view
Login to download
  • 00:04 In this video we're going to look at a cool technique called Custom Views,
  • 00:08 which allows us to prepackage the way that we want to display our spreadsheet.
  • 00:13 So we could go and hide certain rows and columns and give us multiple ways to very
  • 00:17 quickly flip between the different views that we wanna show somebody.
  • 00:21 Now, every custom view is actually set up through the View tab,
  • 00:26 under the Custom Views button.
  • 00:28 And while you normally wouldn't see views in this list if you haven't set them up,
  • 00:32 this workbook happens to have three of them in it already and
  • 00:35 I'd like to get rid of them.
  • 00:36 Unfortunately, you can't select all of them at once,
  • 00:38 we have to do it individually.
  • 00:40 But if you need to delete a custom view, we're just gonna go and
  • 00:42 click the Delete button and say Yes for each individual item.
  • 00:46 And fortunately, there's only three, so it doesn't take that long.
  • 00:50 Now, the next thing that I wanna focus on is creating a custom view.
  • 00:54 The particular view of the spreadsheet that we're looking at right now
  • 00:57 is where all rows are expanded.
  • 00:59 We see all data.
  • 01:00 And I'm gonna set up a custom view right now called Full
  • 01:04 that will actually allow me to come back to this view at any point in time.
  • 01:07 So there we are, it's gonna be Full.
  • 01:09 You'll notice that we can actually change the print settings and include them or
  • 01:12 not, as well as the hidden rows, the columns, and filter settings.
  • 01:16 I'm now gonna say OK, and nothing really looks any different.
  • 01:20 But now what I'm gonna do is I'm gonna ask you to create an alternate view of this.
  • 01:24 So I think I'm gonna go and say, you know what, let's collapse this down to number 2
  • 01:29 here, and then we'll collapse this down to number 1.
  • 01:31 So at this point we now have a collapsed view of our income statement.
  • 01:36 Perfect, this is the perfect candidate for another custom view.
  • 01:40 We'll go to Custom Views and we'll set up a new one, call as Add and
  • 01:45 we'll call this one Collapsed, and say OK.
  • 01:51 We now have two views in the workbook and the reason why this is so
  • 01:55 amazing is because I can easily go to custom views and
  • 01:58 I can say, grab Full and Show and it opens it up.
  • 02:03 Or Custom Views, Collapsed, and show and it'll toggle between them.
  • 02:09 So as many different pieces as I do in here,
  • 02:11 they're all gonna be captured in the view, but what if I wanna change the view?
  • 02:15 Maybe, for example, I'm not really happy with seeing these headers up here.
  • 02:20 So what I'm gonna do is I'm gonna go right-click and choose Hide.
  • 02:26 And I'd like to replace my Collapsed view with this view.
  • 02:30 No big deal.
  • 02:31 We'll go to Custom Views, we'll go to collapsed, and we're gonna say Add.
  • 02:36 And it says, okay, what's the name of the view that you want to work with?
  • 02:39 I'm gonna type in the same name and say OK.
  • 02:43 And it says, it already exist, do you wanna delete it?
  • 02:45 Yes, and it will recreate the new view.
  • 02:49 Let's go and check out Full, Show.
  • 02:53 And say, yeah you know what,
  • 02:54 I don't really wanna see these headers here either, so
  • 02:56 we'll go right-click and say Hide.
  • 02:59 Custom views, let's go and say Add, and we'll choose Full.
  • 03:07 It already exists, we'll replace it.
  • 03:10 Custom Views, we'll go look at the Collapsed view, and
  • 03:12 you'll notice that we've updated it.
  • 03:14 So those new headers don't show up now.
  • 03:17 Now, they're still there, of course.
  • 03:18 I can easily go back to the custom view for Full, Show it.
  • 03:22 They don't show, but if I come back up here, right-click, and say, Unhide,
  • 03:27 those rows, of course, will come back.
  • 03:29 So Custom Views are a phenomenal way of being able to go and
  • 03:33 prepackage the way you wanna show your data.
  • 03:35 It doesn't protect any data, it doesn't really get rid of any data, but
  • 03:38 it allows you to actually control what people are looking at.
  • 03:41 So if you're in a board meeting and you need to look at different collections of
  • 03:44 the way things are organized, no problem.
  • 03:46 Custom Views, fantastic tool to make that happen.

Lesson notes are only available for subscribers.

Gift this course