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
10.9 KB Custom Views - Completed.xlsx
12.5 KB Custom Views - Extra Practice.xlsx
15.3 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 Where Custom Views really shine in Excel is when you're trying to get your work formatted into a specific format potentially for printing
  • 00:10 or for showing somebody on screen so they see the things that they need and they don't see the things they don't.
  • 00:17 In this particular case we've got an income statement that's compressed right now with outlining.
  • 00:22 We'll set up some custom views so we can very quickly toggle between a fully expanded view
  • 00:28 like this that has all of the rows showing
  • 00:31 and the compressed view that we just saw.
  • 00:33 So it's an easy thing to do. What we do is we go to the View tab
  • 00:38 and we go to Custom Views.
  • 00:41 That pulls up a little dialog box for us and what we're going to do is we're going to Add a new view and I'm going to call this one Full.
  • 00:48 Say OK
  • 00:50 and basically what's happened is we just created a view that every time we toggle it will show us a fully expanded view.
  • 00:57 Now lets go and set up another one, a compressed view. Click on number 2 collapse all
  • 01:01 these things down with their outlining and we'll go back to Custom Views
  • 01:05 and we'll say Add
  • 01:08 and we'll call this one Compressed.
  • 01:14 So now when we want to see our full view
  • 01:17 we can go and say Full, Show
  • 01:20 and it'll automatically show us everything
  • 01:24 or we can go back and we can say Compressed
  • 01:27 show and it'll show us our compressed view. Now this is easy because we're just
  • 01:31 pressing two or three. We can do that but you know what this compressed view is not perfect.
  • 01:36 The compressed view shouldn't really have the details of revenues on these either. So what I really want to see is I want to see it looking like
  • 01:43 this and that way I actually have something that's a little bit more than a couple clicks
  • 01:47 to try and get to. So how do I modify the custom view to actually make that happen.
  • 01:52 Well I'm going to go back to Custom Views
  • 01:55 and I'm going to take a look at my compressed view.
  • 02:00 And I'm going to say Add.
  • 02:02 And I'm going to call this one
  • 02:04 Compressed. Exact same spelling and say OK.
  • 02:09 It tells me it already exists, would I like to delete it and continue? Yes I would.
  • 02:14 At that point what it does is it actually overwrites the compressed view so if I go back to Full and I say Show
  • 02:21 it'll actually list the whole thing for me again.
  • 02:24 If I go back into Custom Views and I go to compressed and show it
  • 02:29 it now compresses the whole thing down.
  • 02:33 So really really handy trick you can do this, you can hide your columns, you can hide individual rows, you can use outlining, you can use all
  • 02:41 kinds of different toolsets to actually show your data exactly the way you want.
  • 02:46 What you see here will print when you actually try and print it.
  • 02:50 What you see here will actually show on screen. So if you're trying to review a price list
  • 02:53 with someone or maybe you just have a huge set of data. You can hide off all the individual
  • 02:57 columns you don't really want to see or all the individual rows you don't want to see
  • 03:00 For that specific view you're working with. So these can get as complicated or as uncomplicated as you like
  • 03:05 but definitely it's a handy handy tool for very quickly flipping back and forth between information.

Lesson notes are only available for subscribers.

Gift this course