Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Archive for the ‘Excel 2016’ Category

Build an Excel Navigation Panel

Image result for NAVIGATIONNavigating within an Excel workbook is simple enough if the workbook contains one or two worksheets and if you are familiar with working with multiple worksheets. However, if the workbook as more than a few worksheet or the user is not comfortable with a multiple sheet environment, navigation can be a little more involved, even a little intimidating.

In this article we will discuss creating and using hyperlinked drawing objects to create an intuitive menu system for an Excel Workbook.

 

SNAGHTML9f2d87e

image

Scenario

The workbook we will be enhancing is used to manage attendance records for employees at each of five locations. Each location is represented by one worksheet for each of three shifts plus one worksheet for the location’s employee list. Unless you are working on a very wide monitor and/or at very high resolution, seeing the tabs for all sheets at the same time will be unlikely:

image

So going directly from Location A to Location D, for example will require at least one extra step in addition to a mouse-click on the desired sheet tab. In some Excel version two additional steps are needed. First you need to right-click in the sheet navigation area, then select the desired sheets. In some versions of Excel, you will only see 15 sheet names when you right-click the navigation area and may have to select “More Sheets” to be able to access all sheets.

SNAGHTML4ac3ffd

 

image

A custom navigation pane will make the process of navigating from one location to any other location in the workbook simpler and more efficient that the stock method of selecting sheet tabs.

‘Own-use’ vs. External User

While the same general techniques apply whether you are designing a navigation panel in a workbook solely for your own use or for another person who will be working with the workbook on a regular basis, designing the panel for someone else to use requires more thoughtful and detailed planning.

Objectives

The navigation should be simple to use, have an attractive look and feel that is consistent throughout the workbook, be visible at all times, and require a minimum number of mouse clicks for the user to switch from one worksheet to another.

Create a Prototype Menu

The more time you put into designing and perfecting a prototype menu, the easier it will be to apply the finished menu to the rest of the workbook. Start by creating an additional sheet which you will use as a sort of drawing canvas for creating the prototype menu. In the sample workbook, this sheet is named ‘Menu Prototype.’

If you used consistent naming principles for the worksheets when  you created the workbook, you can take advantage of Excel features like copy and paste and object grouping to take some of the tedium involved with building your prototype menu

  • Drawing Objects

Almost any drawing object is suitable for individual menu elements. Simple rectangles arguably make the most efficient use of available space.

  • Effects

Excel drawing objects, especially in the 2007 and later versions, offer a wealth of visual effects to set them off from normal worksheet contents.

  • Hyperlinks

Hyperlinks can be applied to most drawing objects. Waiting to apply them until you have fixed the basic properties of the object makes them a little easier to work with.

  • Properties

By default, drawing objects are moved and re-sized with cell. To ensure the menu doesn’t get automatically moved or re-sized, change the relevant property.

NOTE: Screenshots in this article were developed using Excel 2016. If you are using an earlier version of Excel, what you see an your screen may look a little different from these illustrations

Step-by-step, here’s how to build a prototype menu.

  1. Create a simple drawing object such as a rectangle. Use your mouse to drag the crosshair ( c ) to draw the rectangle shape. You can refine the size of the rectangle to what works best for you.SNAGHTML5a89b04

SNAGHTML5a949b3

After you have drawn the shape, click to select it

image

 

and set the height and width of the object on the Drawing Tools Format Ribbon Tab ( f ).

image

2. With the object selected type a caption that will indicate where the button will take the user when they click it. Use the Home Tab’s alignment buttons to align the caption within the button.

SNAGHTML955626f

3. Duplicate the button. You can use copy and paste but it’s faster to use Ctrl D (duplicate). You will need as one copy of the button for each option you want to offer the user. You will need to modify the caption for each button but it is faster to do this after you have made all duplicates you will need.

TIP: In some versions of Excel, if you position the first duplicate where you want it relative to the original, then continue duplicating, additional duplicates will automatically position themselves relative to the previous duplicate.

4. If necessary, drag the buttons roughly to the positions you would like them to be relative to each other. Use the Align commands on the Drawing Tools/Format Ribbon tab to refine their alignment. Select all of the buttons and use the distribute commands to make the layout more symmetrical.

image

image

5. When you have the buttons laid out to your satisfaction, with all the buttons selected use the Group command to combine them into a new custom object.

6. With the new group selected, use shape effects to give your menu panel and buttons a distinct ‘look and feel.’

SNAGHTML98f8946

 

image

7. With the menu group still selected right-click it and select size and properties. In the size and properties pane (or dialogue, depending on your Excel version), set the size property to “Don’t move or size with cells.”

image

 

8. De-select the group and then select each button in turn to assign its hyperlink.

SNAGHTML9a9572b

Choose “Place in This Doucment ( a )," then select the name of the sheet to which you want this button to lead ( b ). Type the name of the cell you want to activate on the selected sheet ( c ). Then click the ScreenTip button and enter the tip you would like to pop up when the user’s mouse hovers over the button.

image

Pay close attention to all the details when you set up your prototype menu. Once you are satisfied that it looks and performs as you want it to, simple copy and paste it to every worksheet in the workbook.

Apply the Menu to all Worksheets

Positioning the menu panel in a consistent location on every worksheet will give your workbook a more professional appearance. Here is a horizontal menu located at the top left or a worksheet.

image

And here is a vertical menu, also located at the top left of a worksheet.

 

image

You can also remove the hyperlink from the button that represents the sheet on which a menu panel is located and ‘grey-out’ the caption for that button.

image

For horizontal menu panels, set the height of Row A to the height of the menu panel. For vertical menu panels, set the width of Column 1 to the width of the menu panel. If you want to prevent then menu panel from scrolling off, use the freeze panes command to lock the top row or first column of each worksheet.

Advantages

  • Intuitive
  • Makes navigation easier
    • Direct access to specified worksheet
    • Direct access to specific location, if needed

Disadvantages

  • Takes time to plan and create
  • Sacrifices some workspace

A hyperlinked menu navigation panel will simplify moving from sheet to sheet in workbooks that have more than a few worksheets.

Build an Excel Navigation Panel

Image result for NAVIGATIONNavigating within an Excel workbook is simple enough if the workbook contains one or two worksheets and if you are familiar with working with multiple worksheets. However, if the workbook as more than a few worksheet or the user is not comfortable with a multiple sheet environment, navigation can be a little more involved, even a little intimidating.

In this article we will discuss creating and using hyperlinked drawing objects to create an intuitive menu system for an Excel Workbook.

 

SNAGHTML9f2d87e

image

Scenario

The workbook we will be enhancing is used to manage attendance records for employees at each of five locations. Each location is represented by one worksheet for each of three shifts plus one worksheet for the location’s employee list. Unless you are working on a very wide monitor and/or at very high resolution, seeing the tabs for all sheets at the same time will be unlikely:

image

So going directly from Location A to Location D, for example will require at least one extra step in addition to a mouse-click on the desired sheet tab. In some Excel version two additional steps are needed. First you need to right-click in the sheet navigation area, then select the desired sheets. In some versions of Excel, you will only see 15 sheet names when you right-click the navigation area and may have to select “More Sheets” to be able to access all sheets.

SNAGHTML4ac3ffd

 

image

A custom navigation pane will make the process of navigating from one location to any other location in the workbook simpler and more efficient that the stock method of selecting sheet tabs.

‘Own-use’ vs. External User

While the same general techniques apply whether you are designing a navigation panel in a workbook solely for your own use or for another person who will be working with the workbook on a regular basis, designing the panel for someone else to use requires more thoughtful and detailed planning.

Objectives

The navigation should be simple to use, have an attractive look and feel that is consistent throughout the workbook, be visible at all times, and require a minimum number of mouse clicks for the user to switch from one worksheet to another.

Create a Prototype Menu

The more time you put into designing and perfecting a prototype menu, the easier it will be to apply the finished menu to the rest of the workbook. Start by creating an additional sheet which you will use as a sort of drawing canvas for creating the prototype menu. In the sample workbook, this sheet is named ‘Menu Prototype.’

If you used consistent naming principles for the worksheets when  you created the workbook, you can take advantage of Excel features like copy and paste and object grouping to take some of the tedium involved with building your prototype menu

  • Drawing Objects

Almost any drawing object is suitable for individual menu elements. Simple rectangles arguably make the most efficient use of available space.

  • Effects

Excel drawing objects, especially in the 2007 and later versions, offer a wealth of visual effects to set them off from normal worksheet contents.

  • Hyperlinks

Hyperlinks can be applied to most drawing objects. Waiting to apply them until you have fixed the basic properties of the object makes them a little easier to work with.

  • Properties

By default, drawing objects are moved and re-sized with cell. To ensure the menu doesn’t get automatically moved or re-sized, change the relevant property.

NOTE: Screenshots in this article were developed using Excel 2016. If you are using an earlier version of Excel, what you see an your screen may look a little different from these illustrations

Step-by-step, here’s how to build a prototype menu.

  1. Create a simple drawing object such as a rectangle. Use your mouse to drag the crosshair ( c ) to draw the rectangle shape. You can refine the size of the rectangle to what works best for you.SNAGHTML5a89b04

SNAGHTML5a949b3

After you have drawn the shape, click to select it

image

 

and set the height and width of the object on the Drawing Tools Format Ribbon Tab ( f ).

image

2. With the object selected type a caption that will indicate where the button will take the user when they click it. Use the Home Tab’s alignment buttons to align the caption within the button.

SNAGHTML955626f

3. Duplicate the button. You can use copy and paste but it’s faster to use Ctrl D (duplicate). You will need as one copy of the button for each option you want to offer the user. You will need to modify the caption for each button but it is faster to do this after you have made all duplicates you will need.

TIP: In some versions of Excel, if you position the first duplicate where you want it relative to the original, then continue duplicating, additional duplicates will automatically position themselves relative to the previous duplicate.

4. If necessary, drag the buttons roughly to the positions you would like them to be relative to each other. Use the Align commands on the Drawing Tools/Format Ribbon tab to refine their alignment. Select all of the buttons and use the distribute commands to make the layout more symmetrical.

image

image

5. When you have the buttons laid out to your satisfaction, with all the buttons selected use the Group command to combine them into a new custom object.

6. With the new group selected, use shape effects to give your menu panel and buttons a distinct ‘look and feel.’

SNAGHTML98f8946

 

image

7. With the menu group still selected right-click it and select size and properties. In the size and properties pane (or dialogue, depending on your Excel version), set the size property to “Don’t move or size with cells.”

image

 

8. De-select the group and then select each button in turn to assign its hyperlink.

SNAGHTML9a9572b

Choose “Place in This Doucment ( a )," then select the name of the sheet to which you want this button to lead ( b ). Type the name of the cell you want to activate on the selected sheet ( c ). Then click the ScreenTip button and enter the tip you would like to pop up when the user’s mouse hovers over the button.

image

Pay close attention to all the details when you set up your prototype menu. Once you are satisfied that it looks and performs as you want it to, simple copy and paste it to every worksheet in the workbook.

Apply the Menu to all Worksheets

Positioning the menu panel in a consistent location on every worksheet will give your workbook a more professional appearance. Here is a horizontal menu located at the top left or a worksheet.

image

And here is a vertical menu, also located at the top left of a worksheet.

 

image

You can also remove the hyperlink from the button that represents the sheet on which a menu panel is located and ‘grey-out’ the caption for that button.

image

For horizontal menu panels, set the height of Row A to the height of the menu panel. For vertical menu panels, set the width of Column 1 to the width of the menu panel. If you want to prevent then menu panel from scrolling off, use the freeze panes command to lock the top row or first column of each worksheet.

Advantages

  • Intuitive
  • Makes navigation easier
    • Direct access to specified worksheet
    • Direct access to specific location, if needed

Disadvantages

  • Takes time to plan and create
  • Sacrifices some workspace

A hyperlinked menu navigation panel will simplify moving from sheet to sheet in workbooks that have more than a few worksheets.

Excel 2016 A Small Surprise with Autofill

Autofill has long been one of my favourite Excel productivity features. It is the fastest way I know to copy cell contents to multiple cells. If you have an existing column of data and want to one or more formulas to adjacent columns, double-clicking the fill handle adds a whole new layer of productivity.

This video illustrates this tip and the small surprise that Excel 2016 introduced that affects this feature. By default, autofill reproduces a series; in other words if the cell that you want to use as the source data for autofill contains a value like, Room 101, autofilling this cell down a column will give new values like Room 102, Room 103, Room 104 ….

That’s all well and good, but what if you wanted to assign a number of people to the same room, Room 101? In previous versions of Excel, immediately after autofilling, Excel displayed the Paste Options button. The surprised that Excel 2016 introduced is that the default does not display Paste Options.

That can prove very frustrating, especially if you autofilling cells across may rows to many columns. Fortunately, the solution is relatively simple: enable the Paste Options button by modifying your Excel Options to Show Paste Options button when content is pasted.

image

Check it out!

Are You a OneNote Fan?

Have a look at this article. It has some valuable tips on note-taking. Although the article is addressed to medical students, anyone who needs to take notes of any kind will find some excellent tips here.