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
15.4 KB Custom Views - Completed.xlsx
15.5 KB

Quick reference

Topic

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 explore Custom Views.
  • 00:07 Now, Custom Views are a cool technique that we can use to build a pre-package
  • 00:11 view of our spreadsheet, including the hidden columns or rows.
  • 00:16 This is great because, as we define these different views,
  • 00:19 we can lay out the ones that are optimize for specific circumstances, and be able
  • 00:24 to call them up really quickly, ready to go, when we want to print them or whatnot.
  • 00:29 Now, to create a Custom Views, they're always done threw the View tab.
  • 00:33 You'll notice right now my data here is a fully expanded list of
  • 00:37 all of the transaction rows for my profit or loss statement.
  • 00:41 So I'm gonna set up a Custom Views to preserve this particular look.
  • 00:46 So we'll go to Custom Views, we'll say Add,
  • 00:48 and I need to give it a name of, say, Full.
  • 00:52 In this case, you can see that we have print settings,
  • 00:54 it's also controlling our hidden rows, columns, and filter settings.
  • 00:58 We'll say OK, and it doesn't look like anything's really changed and
  • 01:02 in truth it hasn't.
  • 01:03 So, let's go and collapse some things down a little bit.
  • 01:06 I'm gonna click the two in my grouping levels here, and collapse my Costs down.
  • 01:11 And I'm gonna create a new view for this one called Compressed.
  • 01:15 So go to Custom Views, we'll say Add,
  • 01:18 we'll call this one Compressed, we'll say OK.
  • 01:25 Again, it doesn't look like anything's changed except that
  • 01:28 when we go to Custom Views now.
  • 01:30 I can say Full > Show.
  • 01:34 And it will expand all of those rows.
  • 01:38 If I want to go and see collapsed, open the Custom Views, and
  • 01:42 will go to Compressed > Show, and it collapse it down.
  • 01:47 Under this point I look out and go,
  • 01:48 you know what may be this is exactly what I meant.
  • 01:51 I don't really need to see the detail of the revenues here.
  • 01:54 I wanna collapse this down and
  • 01:56 I kind of wish I'd made my compressed view look like this.
  • 01:59 So to fix that, what we're gonna do is we can go to Custom Views.
  • 02:03 Now we could delete the compressed view and start over or
  • 02:07 we could say Add, and say Compressed.
  • 02:13 And say OK. And it says it already exists.
  • 02:16 Do you wanna delete it and continue?
  • 02:17 Yes, we will.
  • 02:19 And now you'll see that we still have the compressed view, so
  • 02:21 we can show the full view.
  • 02:24 And we can go back to our new compressed view.
  • 02:29 Now one of the things I do wanna point out in this is that,
  • 02:32 if you have your full supplier part list here, or whatever else, and you filter,
  • 02:35 you make a Custom View to only show one person's information that you need to see.
  • 02:40 You don't wanna send them the workbook without Custom View active
  • 02:42 because they can still unhide all the stuff and get into that original data.
  • 02:46 So this is not a security measure.
  • 02:48 What this is though, is it's a way that you can control what's showing on screen.
  • 02:52 If you need to put something in front of your boss and
  • 02:54 they don't wanna see the detail lines, then here you go.
  • 02:57 This field work great.
  • 02:58 If you need to see the detail lines cuz you need to do further analysis,
  • 03:01 you can quickly flip over to the Full view, and say Show.
  • 03:05 So, it's a very simple technique, but it's something that can very, very handy for
  • 03:09 us as we're actually building and
  • 03:11 needing to step into and out of views that we actually have in our workbooks.

Lesson notes are only available for subscribers.

Gift this course