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

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.

dBase Support Returns to Access

Long before Access came on the personal computer scene, there was dBase. Ultimately, several other data management systems used the same .dbf file format, including FoxPro and Paradox.

From time to time, Access developers may be working with clients who want to incorporate legacy date currently stored in dBase (.dbf) format into an Access project. Some time ago, Access dropped .dbf support. Now we have good news, .dbf support has returned to the Access platform.

A Place for Everything… (Part 2)

In Part 1 of this article, I discussed the information management needs for my training practice. Now I will turn to the first major component of the system – managing correspondence and applying OneNote® to the task.

Image result for puzzle

With vary rare exceptions all correspondence for my training practice is electronic – email. So the primary purpose of the system is organizing and managing email. At the same time, the system needs to allow for possible correspondence in other formats, particularly telephone calls and text messages.

Guiding Principles

In Part 1 of this article, I referred to the system’s “prime directives”

  • ease of storage
  • ease of retrieval

The system is intended to manage information

  • received from, and sent to, other people

and

  • about training workshops and people attending.

Dealing with training workshops and the people who attend the workshops suggests a general structure (or categories) for organizing the information:

  • Sponsors
    • Years
    • Workshops
    • Trainees
  • Information Requests

Why OneNote

Given that most of the information I am dealing with comes in the form of e-mail, it is fair to ask why go beyond my email client, Outlook®? After all, Outlook does offer custom folder, and fairly extensive search tools. Couldn’t you organize all the information in Outlook and avoid transferring information to and from another tool?

The answer is, of course, “Yes …. but….”

As good as Outlook’s folder management and search tools are, I found they weren’t quite the tool I was looking for. For one thing, my emails deal with more than just training-related information. As my training practice grew, it became increasingly time-consuming to track down all the emails related to, say, a specific workshop or a specific sponsor.

Some years ago, I discovered OneNote® and had been using it to store and manage much of the information that comes my way on a daily basis. For example, I uses a variety of computer applications. I use a OneNote notebook to keep track of the license information for these applications.

So I began thinking about using OneNote to manage my training practice information. Communication between Outlook and OneNote is relatively easy; versions of Office since 2010 or so have included an Outlook command to send the current item to OneNote. The same versions include a ‘printer’ which means you can create a OneNote entry for just about anything simply by printing the item and selecting “Send to OneNote” as the printer.

Setting it up in OneNote

Image result for notebookThe OneNote features I will be discussing here specifically refer to OneNote 2016 running on a Windows 10 desktop computer. The organization of OneNote notebooks is relatively simple.

A OneNote can have one or more sections. Each section can have one or more pages. For my purposes, the most useful structural feature of notebooks is section groups. It is this feature the is at the heart of my filing system.

When you first create a OneNote notebook, it has one section with one page in it. In my training notebook I will use that section  as a table of contents for the entire notebook. To identify the section and its purpose, I have renamed it ‘Notebook Contents.’

I should mention at this point that I use the Onetastic add-in TOC in Current Notebook macro to automate this part of the notebook. The table of contents supports the ease of retrieval prime directive. The Notebook Contents section contains a single page, ‘Table of Contents,’ generated by the Table of Contents macro. When I add new content to the notebook, I use the macro to generate a new Table of Contents page and delete its predecessor.

The notebook has a second ‘top-level’ section named, ‘Information Requests.’ This section contains one page for each training information request I receive. I use another Onetastic macro, TOC in Current Section, to generate a Table of Contents list of current requests in the section.

The meat and potatoes of the notebook is a section group I have called Sponsors. Each sponsor is represented by its own section group with an index section, and one section group for each training year.  Each training year has one section for correspondence to and from this specific sponsor during the year and a section group for workshops. Finally the Workshops section group has individual sections for each workshop.

One possibility I considered was to have two section groups for workshops, pending and completed but I decided to simply use colour coding to make the distinction.

Believe me this system is far more complicated to write or read about, so here is a chart outlining everything:

image

Notice that the contents are blue and underlined? Each entry in the generated table of contents is a hyperlink to the section listed. That makes it easy to get to any particular section or page.

 

How it Works on a Daily Basis

Here’s how the workflow goes.

I receive an email from Mary Smith asking for general information. After reading her email in Outlook, I click the Send To OneNote command and choose the Training Notebook Information Requests section as the destination.

Now, let’s skip ahead in time a bit to when Mary books a training date. Since I don’t yet have a section for the company that will be sponsoring the training, I create new section in the Sponsors section group for DEF Inc. At this point I name the section that OneNote automatically added to the DEF section group for 2016, name its first section Correspondence and create a Workshops section group.

In the Workshops section group, I name the first section Workshops and create a new section named for the Workshop that Mary has booked.

Now, if all of that sounds time consuming, it really isn’t. All it takes is a couple of minutes.

Since I’d like to keep all correspondence related to a particular sponsor together, at this point I will move Mary’s information request to the new correspondence section for this year for the sponsor. OneNote make it easy to relocate pages ‘on the fly.’

I should also note that, while the Onetastic TOC macros are a great tool, this system does require some manual work for some of the sub-indexes. Once again, OneNote makes creating links to specific pages pretty much child’s play.

 

So that is my correspondence management in a nutshell, albeit a somewhat large nutshell. Part 3 of this article will discuss the building data management component of my system.

Access Now Included in Office 365 Roadmap

Exciting news for the Access community – for the first time Access now appears in the Office 365 Roadmap. See this UtterAccess news announcement. TheSmileyCoder, one of my UtterAccess colleagues has posted a review here. Use this link to go directly to Access features currently listed on the Roadmap.

A Place for Everything …

imageThe timeless proverb, sometimes attributed to Benjamin Franklin, A place for everything and everything in its place, is a simple principle, one that is at the heart of getting and staying organized. For several years now, one particular application has been at the heart of managing much of the information that I need to keep a tight rein on. That application is Microsoft OneNote®.

In this article I am going to discuss how I use OneNote to manage vital information for business. On any given day, I may wear one or more ‘hats,’ each hat representing one aspect of my business which includes, Access database development and consulting, Excel worksheet consulting, and Microsoft Office® Suite user training.

Scenario

In my training practice (Sudbury Training), which I will focus on in this article, I work with companies and individuals to help the people who attend one-day workshops improve their skills in using Microsoft® applications (Word, Excel, PowerPoint,Access, Publisher, Project, Visio, OneNote, Outlook) in their daily work. From a business perspective, I am dealing with two main groups: clients (the business or individual paying for the service) and trainees (the individuals to whom I directly deliver the service.) Regardless of whether a particular workshop is ‘once-only’ or repeat business, there is a general pattern for the information I need to manage. OneNote® makes it easy to set up a system that makes sense to me in this context and  that mimics (or models) the real world setting of the information.

Before that can happen, of course, it is necessary to determine what general categories describe the information to be managed. There is really no single right way to do this. The guiding principle, at least in a ‘one-man show’ is what works best for the person who will maintain and use the information. In a larger enterprise, of course, the guiding principle has to be what will work best for the organization as a whole.

 

That said, the categories I came up with for my business are:

  • Clients
  • Correspondence
  • Workshops
  • Attendees
  • Scheduling

    Image result for nuts and bolts

Figuring out the System

The “prime directive” of any information management system is that recovery of any single piece of information stored in the system must be simple and efficient. If it takes a dozen commands and many minutes to find out when a workshop is running and how many people are attending, that is a pretty good clue that the system in question is not ideal.

Second only to ease of retrieval, is ease of storage, getting information into the system so that it can be retrieved as and when it is needed.

In the course of analyzing my data management needs, it quickly became apparent that what I needed was a two-pronged approach, one for storing and retrieving correspondence and one for storing, retrieving, and analyzing facts and figures.

Why the separation? From a data management point of view, correspondence is messy. The vast majority of my business correspondence is e-mail. In general, the pattern starts with a query from a client about workshop availability followed by response to the client with availability details, follow-up discussions and clarifications, formal quotation, and scheduling. Each e-mail in the sequence contains varying amounts of verbiage and significant detail.

While the full content of each email may be more or less important from a documentation point of view, the vital who, what, where, and when details are but a small part of the whole. That’s where the separation comes into play:

  • Correspondence manager for communications received and sent
  • Data manager for significant details

I will leave discussion of the data manager aspects for another article.

In part 2 of this article, I will discuss using OneNote® as the correspondence manager.

Recommended Read

My UtterAccess colleague, Grover Park George, just posted this thoughtful article discussing his mindset, underlying all of his development work. Whether you are a developer or a developer’s client, this article is valuable food for thought.

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.

Excel Copy and Pasting Functionality

Familiarity with Excel’s copy/paste capabilities will significantly improve your ability to get more done in less time. Copy/paste seems simple enough but the feature offers far more than the simple name suggests. In this article, guest blogger Nick Williams explores copy/paste in depth.Nick is an Access tutor based in the U.K.

Copy and pasting is always a useful way of moving data from one location to another, however, in Excel it can be so much more. The copy as picture and paste special options add some really useful functions, yet many people are not aware of their capabilities.

Copy as picture

The copy as picture feature does exactly what it says, allowing part of the worksheet to be copied and pasted elsewhere a picture. This is particularly useful for taking snapshots when working with data that updates frequently. It is also useful when pasting data into another program, for example, Word or PowerPoint, as when copied as a picture, your table or chart will look exactly as it does in Excel when pasted, saving the need to tidy it up. The copy as picture feature can be accessed through the drop down menu next to the copy icon on the Home ribbon.

Paste special

The standard paste tool will copy both the design and contents of a cell or selection of cells. Whilst more often than not this is what most people want to do, there are times when the additional features of paste special are handy. It can be accessed through the drop down menu below the paste icon on the Home ribbon.

The options available upon clicking on paste special will vary depending on what’s currently stored on the clipboard. An image will not offer any choices beyond what type of image to paste it as, whereas a cell copied from elsewhere within Excel will offer plenty of options and is where paste special is most useful.

When pasting a cell or group of cells from within Excel, there are two main option groups; paste and operation. The various paste options allow the user to pick what features from the cell they are copying to paste. The operation option allows basic mathematical operations to be applied to cells.

clip_image001

Paste Options

The below example aims to demonstrate how some of the paste functions work. Column A simply shows the numbers 1 to 5 with one decimal place, with cell A7 summing these 5 numbers with a simple formula. Some basic formatting to the background colour and cell borders has been applied and there is conditional formatting in place giving cells with a value of less than 3 a bold, red font. There is also a comment on cell A4.

clip_image003

All – Copies everything in the cell; the content, visual formatting, number formatting and comments. The pasted cells in Column B of the example appear exactly as they do in Column A, other than the width of the column itself.

Formulas and Values – In the example above, the formulas and values columns look identical to one another. The numbers from Column A have carried over into Column C and Column D, however the visual and number formatting have not, nor has the comment. The difference between the two is the formulas are copied if Formulas are selected, but not when Values are selected i.e. the formula bar for cell C7 would show =SUM(C2:C6) whereas the formula bar for Cell D7 would simply show the number 10. Replacing formulas with numbers can be a good way of getting a snapshot of data in a spreadsheet that is updated frequently.

Formats – Selecting formats will see the visual formats carried over (including conditional formatting), but neither the data nor comments are copied with it. Number formatting is not copied, so if the number 1 was to be entered into one of the cells in Column E in the above example, it would show as 1 rather than 1.0.

Comments – This will paste only the comments, and not any of the formatting or data contained in the copied cell, a great time saver if the same comment needs to be used repeatedly throughout a spreadsheet.

Validation – It is difficult to show validation on the example as it works in the background. It does what it says, and pastes only validation rules from the copied sell into the pasted cell. If the cell being pasted into already contains a value that does not meet the validation rules, it will not be flagged up unless the cell contents are edited.

All using Source theme – This is difficult to show in the example as it only works if pasting into a different spreadsheet. As well as formatting cells individually, it is possible to set a theme for the whole spreadsheet which will control things like font and colour. When copy and pasting data from one spreadsheet to another, formatting set by the theme will not be copied unless this option has been selected.

Column widths – Pasting column widths allows the width of one column to be applied to another without affecting any of the data or formatting in it. Pasting column widths so that they are all equal can make a spreadsheet look much neater. In the example, Column H, where column widths have been pasted, is the only column with the same width as Column A.

Formulas and number formats – As with pasting a formula, this option will paste the formula from one cell to another. The difference here is that number format, for example currency or number of decimal places, will also be copied.

Values and number formats – Similarly, pasting values and number formats will keep the number formatting, but will convert any formulas to values.

All merging conditional formats – Although conditional formatting is pasted along with any other formatting, it will override any conditional formatting already contained in the cells. This option allows the conditional formatting in both the source and destination cells to be applied. In the above example, Column K had a conditional format applied telling it to make all cells with a value of less than 3 brown. Rather than overwrite this, both this conditional format, and the one in the copied cells turning the font bold and red have been applied.

Operation Options

The example below shows how the operations work within paste special. They will add, subtract, multiply or divide the value in the pasted cell by the value in the copied cell. In the example, Columns D to G originally contained the numbers 10, 20, 30, 40 and 50, as shown in Column C. When copying the number 10 from Cell A2 and pasting into Columns D to G using a mathematical operation, the number 10 is added, subtracted, multiplied or divided from the original number.

clip_image005

Other Options

Skip blanks – This is something that may be used when copy and pasting multiple cells. If one of the copied cells is blank, Excel will not overwrite data in a pasted cell with a blank, rather it will leave the contents as they were originally, while overwriting any cells where the copied cells do contain data. Cell C4 in the below example shows how when a blank cell is copied and skip blanks is selected from the paste special menu, the original contents of the cell remain.

clip_image007

Transpose – Transposing data allows rows to be turned into columns and vice versa.

Paste link – Rather than paste the value or formula that is in a cell, selecting paste link will create a formula linking to the data from the copied cell, rather than the actual formula or data it contains. This is useful if the data is likely to change but has to appear in multiple places throughout a workbook.

Creating User Defined Functions in Excel

Welcome back guest blogger, Nick Williams. In this article he discusses the intricacies of User Defined Functions. UDFs are arguably the single most important feature of Excel because it adds virtually infinite depth to Excel’s functionality, supplementing the already extensive list of ~350 built-in functions. The only limit on what you can do with UDFs is the depth of your imagination. Nick is an Access tutor based in the U.K.

Creating User Defined Functions in Excel

Most users are familiar with at least some of the functions built into Excel, for example SUM and AVERAGE. Whilst these are useful tools, there are times when they are not capable of performing some of the more specialised tasks that advanced Excel users require. Creating a custom function is a way of performing a calculation over and over again without the need to run a macro each time or create a series of complicated formulas.

Enabling the Developer tab

User defined functions are created in the Visual Basic Editor, which can be accessed from the Developer ribbon. If the developer ribbon is not visible, it can be enabled by going to the File menu and choosing Options. Select Customize Ribbon from the list on the left hand side, and ensure that there is a tick next to Developer on the list of tabs on the right.

 

image

 

Creating a new module

The Visual Basic Editor can be opened by pressing Alt+F11, or by clicking the Visual Basic Editor icon on the Developer ribbon. Once in the Visual Basic Editor, the first step towards creating a custom function is to create a new module. Although it is possible to add a function to an existing module, putting it in its own module makes it easy to export and reuse in another workbook.

To create the new module, from the menu click Insert, followed by Module. The module should be inserted into the current workbook by default. It is best to store the module within the workbook itself, rather than in the Personal workbook, as anything stored in the Personal workbook is only available to the current user on the current computer. If the file were to be opened on another computer, or even by another user on the same computer, errors would occur wherever the function has been used.

Writing the function

The code for the new function can be entered into the new module window. The actual content will vary depending on what the function is doing, however, it will always start with ‘Function’ at the top and ‘End Function’ at the bottom. The variables used by the function go in the brackets after the function name. The below example is for a very simple function that works out speed.

clip_image006[4]

The first line declares the name of the function (SPEED) and its two parameters (Distance and Time). The ‘As Double’ after each of the variables is telling Excel what type of data to expect from these parameters, in this case a double-precision floating-point number. The second line is the code telling the function how to calculate the value it needs to return. In this basic example it is simply one line stating that speed is equal to distance divided by time. The final line End Function is telling Excel that is the end of the function and there is no more code to run.

In practice, most functions will much longer and more complex than this, as speed is something that could be easily be calculated using a simple formula. Although there is nothing wrong with making calculations in this way, especially if they are being performed over and over again within the document, user defined functions really come into their own when doing more complex calculations, for example where multiple if statements are required.

Using the function

Once the function has been created in the Visual Basic Editor, it can be called up in a workbook in the same way as any of the built in functions. The below shows how the speed of three people has been calculated using the newly created SPEED function. The formula refers to other cells to get the variables distance (Cell B2) and time (Cell C2) and uses these to calculate the speed.

clip_image008[4]

Unlike built in functions, custom functions do not show what variables are required once the open bracket symbol is used. This can however be displayed by pressing Ctrl-Shift-A, however rather than displaying as a tip, the variable names will be entered into the formula bar as a prompt for what they need to be replaced with.

Saving the workbook

As the user defined function is created using VBA code, it will be necessary to save the workbook as a macro-enabled workbook with a .xlsm file extension. When it comes to saving, simply select this as the file type. If this step is not taken, or the workbook had been previously saved as a standard Excel workbook with a .xlsx extension, a prompt will be shown warning that the function will not be saved.

Importing and exporting functions

If a function needs to be used in more than one workbook, rather than rewrite it, it can be exported from the Visual Basic Editor, and then imported into another workbook. When in the Visual Basic Editor, click on the module to be exported and select File > Export file… from the menu. In the new workbook, open the Visual Basic Editor and from the menu select File > Import file… from the menu to import the function.