LESSON WORKBOOK: Level 2 Lesson 10 2007.xlsx
Custom Views can be used on it’s own in conjunction with the Report Manager. The purpose of Custom Views is to save the appearance of a chosen Workbook. This means we can have more than one person work on a Workbook and each person can save their own Custom View. It also saves a lot of time with Print settings that can be very time consuming and fiddly. The types of settings that are retained are:
Row and column settings (width, height, hidden/unhidden)
Zoom settings
Filter settings
Print settings
Worksheet visibility (hidden/unhidden)
Window settings (size)
The use of Custom Views is very simple. You set up your Workbook to look exactly how you want, then simply go to the View tab and under Workbook Views select Custom Views, this will show the Custom Views dialog box. You then click Add and type a name for the Custom View. By default Excel will save the Print settings and any hidden rows or columns, as well as Filter settings. You can uncheck these if you wish. Click OK and that's it. Now whenever you wish to see your Custom View you select Custom Views from Workbook Views on the View tab, select the name of the Custom View you wish to see and click OK. To delete a Custom View select it's name and click Delete. You can save many more than one view per Workbook by simply defining different names for each view.
The Report Manager is very similar to Custom Views and can (and often does) work hand-in-hand with it. Before we start, we may need to install the Report Manger, as it is an Add-in. To do this download the Report Manager Add-in from the Microsoft Web site, http://office.microsoft.com/downloads (search for Report Manager Add-Ins).
Then go to the Office Button>Excel Options>Add-Ins
Ensure Excel Add-Ins is showing in the Manage: box
Click Go.
In the Add-Ins dialog box, click Browse.
Locate and select the file name Reports.xla, and then click OK.
In Add-ins Available, select Report Manager, and then click OK.
The Report Manger will be located under a tab called Add-Ins and is used in conjunction with printing to allow us to set up a template, if you like, of any workbook for printing. This means we can use it to have the worksheets in a workbook always printed in specified sequence with or without a (already set up) Custom View and/or (already completed) Scenarios (a named set of input values you can substitute in a worksheet model). Like Custom Views, Report Manager it is very simple to use.
Go to the Add-Ins tab and select Report Manager from Menu Commands.
Click Add
Type a name for the report in the Report Name box
From the Sheet: box select the name of the Worksheet you wish to add and click Add for each Worksheet you want to add.
Select the name of any Custom Views you have from the View box (if any) and click Add. Repeat this for each Custom View you want to add.
Do the same for any Scenarios you have (these are discussed in our Excel Level 3 course material)
If you wish you can change the order in which the Report will be printed by selecting the appropriate section and using the Move Up and/or Move Down buttons.
When you are ready click OK to go back to the Report Manger dialog box.
You can print your report now (good idea to check it) or come back at any time and print it. As you will no doubt have noticed, you can Add more reports, Delete them or Edit existing ones. You need to remember that any print settings are retained in Custom Views, not Report Manger. So if they were not added to the report your print settings will not be included.
Possibly the best part of these two features are their ease of use, while saving us lots of time.
Hyperlinks supply us with a convenient way to jump to a specified document on a hard drive, network, Intranet or the Internet. We can also use them to go to a specific location within a document or Workbook. There are three methods we can use to insert a hyperlink. They are by selecting the Insert tab then under Links options selecting Hyperlink. Or you can right-click over the cell you want to place the hyperlink in and select Hyperlink. The shortcut Ctrl+K will also insert a hyperlink.
Some uses for hyperlinks might be:
To create a list of internet or Intranet locations
Create a list of workbooks or documents stored on a hard drive or network
Create a list of ranges and/or worksheets in an Excel workbook to jump to easily
Create a list of email addresses
The idea of hyperlinks is to save time and errors.
Let's look at creating a hyperlink in a cell using the Insert Hyperlink dialog box.
The quickest way to display this is to select the cell you want the hyperlink in and push Ctrl+K. We only have two boxes and one option button to work with here so it is very easy.
In the Link to file or URL (Uniform Resource Locator) box either type a valid URL or file path or use the Browse button. For an email address you need to select mailto from the drop-down list, then type the address. If you are using a URL it is usually best to select from the drop-down list or copy and paste it into the box. As we cannot use a right click in this box, all copying and pasting must be done with Ctrl+C and Ctrl+V.
The second box is optional. We can use it to jump to a specific location within the file or URL chosen above. A valid location is any named range, bookmark (Word), database object (Access) or slide number. (PowerPoint). Again you can type, paste or use the Browse button.
Depending what you have placed in the Link to file or URL will determine what will appear when you click the Browse button. For example If you have placed in a Workbook path and clicked Browse Excel will show the Browse Excel Workbook dialog complete with all worksheet names and named ranges for the specified workbook. If we have left the Link to file or URL box blank, the Browse Excel Workbook dialog will be for the active workbook.
The Use relative path for hyperlink means that if we linked to a workbook that is on our hard drive or a network and it was moved to another location, the hyperlink would automatically follow it to its new location. If we clear this checkbox our hyperlink would always point to the same location, regardless whether the workbook still resided in that location or not.
Once we have set our options, we simply click OK and the hyperlink will be inserted in the active cell.
Now all we need to do is click the hyperlink and Excel will take us straight to our specified location. To delete a hyperlink you need to select a cell adjoining it and use the arrow key to select the cell, then either go to Hyperlink under Link options on the Insert tab and select Remove Link or right click and select Hyperlink>Edit Hyperlink, then click Remove Link. You will notice that both of these methods will only remove the Hyperlink and not the text. If you want to remove the text as well you need to go to the Home tab and under Editing options select Clear>Clear All
There is a fourth method for creating a hyperlink is by using the HYPERLINK Worksheet function. The only differences between the two are that the HYPERLINK function allows us to specify a Friendly name. This means instead of having a long URL address we can mask this with any text we want. The other difference is that the URL or File and Location are all stored in the first argument Link _Location. The Excel help gives some good examples of how you would state these. Just push F1 and type Hyperlink
Outlining is a means of viewing summary information as required by collapsing or expanding to hide or display detail.
You can organise worksheet data into summary and detail levels. An example of how this could be used is that you could have numeric data organised into specific groups, such as divisions or departments within your company, sales of a product, sales by salesperson etc. and display or hide the information that you require. You can identify subtotals and hide or collapse detail so that only subtotals appear on the screen. Outlining will also help you make detail data disappear so that only the higher level subtotals remain visible when you are using larger worksheets. An outlined worksheet will print exactly as it appears on the screen. This makes Outlining an extremely useful presentation tool.
You can nominate summary levels and collapse the worksheet so that only the summary sub-totals are displayed; this would be useful if you only wanted to show quarterly totals and not each month. When you Outline a worksheet within Excel, buttons will appear around the rows and/or columns that have been outlined. Buttons with the numbers 1, 2, 3 indicate an outlining level. These buttons can be clicked on to expand or collapse all summary levels to that level. You will also notice the collapse button (-) and an expand (+) button. The collapse button will indicate that the associated summary level has been expanded to show the detail levels. Click the button and you will collapse that summary level and hide the detail levels. The expand button works in the same way. It indicates that the associated summary level has been collapsed to hide the detail levels. When clicked, this button will expand the summary level and display the detail levels.
There are two ways you can create an Outline, automatically or manually. We will first look at creating an Automatic Outline.
If you create an Automatic Outline within Excel, it creates an outline structure based on formulas and their dependent ranges. Formula references must consistently point in the same direction. Usually, summary columns refers to detail columns to the left and summary rows refer to detail rows above.
Try this.
Open the attached workbook Level 2 Lesson 10 2007.xlsx which contains monthly expenditure for a small business.
Select the Automatic worksheet and click in cell A1 (note that it is important that you select a single cell as this will tell Excel that you wish to outline the whole worksheet. If you select a range, only the rows and columns within that range will be Outlined).
Select Group>Auto Outline under Outline options on the Data tab.
Click on the number 1 in the horizontal row of buttons at the left end of the window. Notice how the row details collapse leaving only the Total Expenses row, which is the first heading level) in view.
Click on the number 1 in the vertical column of buttons at the top of the window. You will now notice columns B – I are hidden and only column J (Total) is visible.
Click on the number 2 in the horizontal row of buttons at the left end of the window, then click on number 2 in the vertical row of buttons at the top of the window. Notice now that the secondary level headings are displayed.
Now click on the + (plus) button to the left of Total Labour and notice how the Total Labour details have been expanded.
Click on the + (plus) button above “2nd Quarter” (column I) and notice how the detail has been expanded.
Have a play with the other buttons and see what happens.
If the outline is no longer required, select Ungroup>Clear Outline under Outline options on the Data tab. Note that if you save a workbook with an Outline in it, the Outline will be active when you re-open the workbook.
You can create a Manual Outline yourself by grouping items to your own requirements.
Try this:
Click on the Manual worksheet tab. Notice that this worksheet contains expense forecasts for two expense categories and two divisions.
Select cell A1, then select Group>Auto Outline under Outline options on the Data tab and have a look at how an automatic Outline would look if you used that option.
Clear the Outline by going to Ungroup>Clear Outline
Highlight rows 6 – 18 inclusive. Select Group under Outline options on the Data tab. Notice how these rows are now grouped as the detail rows for the totals showing in row 19.
Highlight both rows 7 and 8 and select Group under Outline options. You will notice how a sub-group has now been created.
Highlight rows 12 and 13 and select Group under Outline options to create another sub-group.
Highlight rows 17 and 18 and select Group under Outline options to create a final sub-group.
Experiment with the various Outline buttons and notice how your data can be expanded and collapsed.
A good tip for when your worksheet is structured in such a way that you require manual Outlining, or you only wish to outline part of your worksheet, is to group in layers, starting from the top and working down. You can then develop a comprehensive system of outlining in your Worksheet with relative ease allowing you to maximum functionality of this useful feature.
So as you can see, Outline view will show you your document structure in a very easy to use manner. Remember, the indentations and symbols in Outline view do not affect the way your document looks in Normal view and do not print.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.