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

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.

Excel Financial Functions–Calculating IRR for Irregular Cashflows

This week, in the final article of this series, guest blogger Nick Williams discusses calculating the Internal Rate of Return for an irregular cashflow. Nick is an Access tutor based in the U.K.

XIRR – Internal Rate Of Return

XIRR function returns the value for an Internal Rate Of Return for a supplied series of cash flows, set of values. The key difference between IRR and XIRR is that XIRR can deal with cashflows that aren’t evenly spaced. Because it can deal with cashflows that aren’t evenly spaced you need to tell is both the series of cashflows and also the dates of each cashflow.

The syntax of the XIRR function in Excel is XIRR (values, dates, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values.

dates is another required parameter, it is a series of dates. You need to have a date that corresponds to each cash flow for the calculation to run. The first date is the date of the investment/loan, and subsequent dates refer to income values.

guess is an optional parameter, and works as it does for IRR. Again, if this parameter is omitted, it is set to 0.1 (10%) by default

Continuing with our previous example, let’s introduce dates into our cash flow set.

We now have dates for the initial investment, and irregular dates of actual and/or forecasted income over the seven years of the project.

clip_image001clip_image003clip_image005

As we would expect, as the returns come sooner on this project the return on it has gone up, when compared with our previous calculation.

If we entered dates that were exactly one year apart for each of the cashflows this would give an answer of 1.82%, the same answer as using the IRR function.

Again continuing with our previous example, lets we add more expected income and periods. Again as the returns come sooner the return on the project has increased when compared to have all of the cashflows spaced exactly one year apart.

clip_image007

WHAT TO DO IF YOUR CALCULATION RETURNS #num!

Microsoft Excel uses an iterative technique for calculating IRR AND XIRR starting from guess. If XIRR can’t find a result that works after 100 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

#NUM! error can also appear if:

1. there isn’t at least one positive and at least one negative value in the list of cashflows

2. any of the specified dates precede the first specified date

3. the specified dates and values arrays have different lengths

XIRR can also return a #VALUE! error if it cannot not recognize any of the specified dates as dates (e.g. you have the value of 20 among your dates array)

Excel Financial Functions–Calculating IRR

This week, guest blogger Nick Williams discusses calculating the Internal Rate of Return for a regular cashflow. Nick is an Access tutor based in the U.K.

IRR – Internal Rate Of Return

IRR returns an interest rate. It is the interest rate that would need to be applied to all of the cashflows in the calculation to give an NPV of zero for the project (see above). In short it tells you the return on the money invested in a project and is often used as an investment decision tool. It is designed to work with a set of regularly spaced cashflows only.

IRR is the same as interest rate of savings or loan, however there is it applied to a very straight forward situation.

The syntax of the IRR function in Excel is IRR(values, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values for the project.

guess is an optional parameter. It is a number that you are guessing is close to the result of IRR. If this parameter is omitted, it is set to 0.1 (10%) by default.

Continuing with our example of above we can see the function in use below.

clip_image002clip_image003clip_image005

Intuitively we know that that is right. Above we discovered that the project had a negative NPV if we applied a 3% discount rate. Therefore we knew that the discount rate to get an NPV of zero had to be below 3%.

Again, as above, lets add two more years of income and see what happens to the result. We got a positive NPV using a 3% rate before so we know that the answer will be above 3% before we start.

clip_image007

Note: Microsoft Excel uses an iterative technique for calculating IRR starting from guess. If IRR can’t find a result that works after 20 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

Excel Financial Functions–Calculating NPV

We’d like to welcome back guest blogger Nick Williams. This week and for the next two weeks, Nick will be publishing a series of Excel financial calculation tutorials. Nick is an Access tutor based in the U.K.

One area where Excel is used extensively is in financial forecasting and modeling. It contains lots of financial formulas which can save hours of time.

As ever the trick is to give Excel the right data in the right way … and then let it do the work for you.

In this article we’re going to have a quick look at three of the basic formulas that Excel has in its kit bag IRR, XIRR and NPV.

NPV – Net Present Value

The NPV returns a value amount. You insert all of the forecast cashflows (both investment and return) relating to a project and also your cost of capital. NPV will then tell you if the project will return a ‘profit’. Profit in this context is that it returns more than your cost of capital for the period that your capital is invested. Put the other way round the value of all of the projects future cashflows, today, is greater than nought.

The basis of the calculation is that a pound now is worth more than a pound in future. Your cost of capital (or discount rate) is the difference in value between a pound now and a pound in a year’s time.

To make this clearer a very simple illustration would be:

Imagine you are investing $10 today. You think that it will return $12 in one year’s time. You like to make at least 10% return on your money when it is invested so your cost of capital is 10%.

The net present value of the $12 today is $10.9 (=£11/(1+10%)) so the $12 in one year is worth more than the $10 today, meaning that you should invest as the project will earn you more than the 10% return that you need.

Syntax of the NPV function in Excel is NPV( rate, value1, [value2], [value3], … )

rate is a required parameter and is the discount rate or cost of capital over a single period.

value1, value2,… are values of future income (positive value) and/or payments (negative value). Value 1 is a required parameter and subsequent values are optional. You will need to put a value, even if it is zero, in for each period of the project.

Below is a more complex example. We have a $200,000 investment which returns different amounts each year for seven years.

We put the initial investment in cell B3. Initially we have the forecasted seven years of income in cells B4:B12. The cost of capital or annual discount rate is in cell B2.

After 7 periods our NPV is going to be:

clip_image001clip_image003clip_image005

If we add income for two additional periods, NPV is going to change:

clip_image007

Five Essential Excel Keyboard Shortcuts

Some keyboard shortcuts are designed to enhance your productivity in specific applications. Here are five that have long been my favourites in Excel.

 

<ctrl>;

Insert the current date. The inserted date will remain the same; it does not update when Excel recalculates formulas.

<shift><ctrl>:

Insert the current time. The inserted time will remain the same; it does not update when Excel recalculates formulas.

<ctrl>HOME

Move the selection to the beginning of the worksheet. Cell A1 becomes the active cell. Note: if freeze panes is in effect, the shortcut behaves a little differently, making the top left cell of the lower right quadrant active. If Row 1 contains the header of a structured table, <ctrl>HOME will select cell A2 rather than A1.

<ctrl>END

Selects the cell at the lower right corner of the portion of the worksheet that has actual contents.

F4

When entering formulas, make a cell reference absolute. Each time you press F4 when you are entering a formula the absolute reference changes from Absolute Cell or Range to Absolute Row/Relative Column to Absolute Column/Relative Row to Relative Cell or Range

Links for the technically inclined:

Announcing the Microsoft Cloud Roadshow

https://microsoftcloudroadshow.com/cities/?Wt.mc_id=dx_MVP400580

This is a free, two day technical training event for IT Professionals and Developers that provides best practices and insight from those who run cloud services across Office 365, Micros oft Azure, and Windows 10.

Developer Interview Series #1 –

The Power of Cross Platform Development with Universal Apps and Xamarin

https://channel9.msdn.com/Blogs/Case-Studies/The-Power-of-Cross-Platform-Development-with-Universal-Apps-and-Xamarin?WT.mc_id=dx_MVP400580

In this interview series, we bring you best practices, anecdotes, and insights from developers who are building creative solutions using Microsoft technologies.

We’re on the road to self-driving business applications

http://blogs.msdn.com/b/stevengu/archive/2015/09/09/we-re-on-the-road-to-self-driving-business-applications.aspx?WT.mc_id=dx_MVP400580

A blog by Steve “Guggs” Guggenheimer on self-driving ERP (Enterprise Resource Planning software)

Memory Compression in Windows 10 RTM

https://channel9.msdn.com/Blogs/Seth-Juarez/Memory-Compression-in-Windows-10-RTM?WT.mc_id=dx_MVP400580

The OS is doing some clever optimizations that allow your processes to trim some of the memory but not necessarily page it out to disk.

Managing hidden apps, beta apps and visibility of in-app purchases in Dev Center

http://blogs.windows.com/buildingapps/2015/09/10/managing-hidden-apps-beta-apps-and-visibility-of-in-app-purchases-in-dev-center/?WT.mc_id=dx_MVP400580

The unified Dev Center introduced several new options to manage the visibility of apps and in-app purchase.

Five Essential Keyboard Shortcuts

ShortcutTrying to learn keyboard shortcuts can be intimidating; there are just so many. Like most problems, however, you can overcome the problem by breaking it down to bite-sized chunks. So if you haven’t been using keyboard shortcuts, start by learning and using just these five. Chances once you have used them only a few times, they will become second nature. Remember that some keyboard shortcuts require a combination of keys. That means you press and hold the first (second, and third, in some cases) and tap the last. As you will see, frequently the key to use makes logical sense but sometimes it doesn’t.

<ctrl>A Select the entire document
<ctrl>X Cut the selected content or object(s)
<ctrl>C Copy the selected content or object(s)
<ctrl>V Paste what you have cut or copied
<ctrl>Z Undo the last action you did

These particular keyboard shortcuts (and many more, actually) have a consistent Beachmeaning throughout the Windows world. Whether you are working with a Word Document, an Excel Workbook, a PowerPoint Presentation, any other Office document, or Windows (File) Explorer, you use the same keyboard shortcuts.

Why bother learning these or other keyboard shortcuts? It’s all about time. To be sure, the time you save by using a keyboard shortcut once is insignificantly brief. But these shortcuts do things that you do frequently and repeatedly whenever you are working on your computer. Think of those tiny slivers of time as grains of sand on a beach. Save enough of them any you will have time for a beach vacation.

Links for the technically inclined:

Announcing the Biggest VM Sizes Available in the Cloud: New Azure GS-VM Series

http://weblogs.asp.net/scottgu/announcing-the-biggest-vm-sizes-available-in-the-cloud-new-azure-gs-vm-series?WT.mc_id=dx_MVP4000580

Important announcement in Azure space

 

Announcing VP9 support coming to Microsoft Edge

http://blogs.windows.com/msedgedev/2015/09/08/announcing-vp9-support-coming-to-microsoft-edge/?WT.mc_id=dx_MVP4000580

New feature for Microsoft Edge

 
 

Unity 5.2 and Visual Studio Tools for Unity 2.1

http://blogs.msdn.com/b/visualstudio/archive/2015/09/08/unity-5-2-and-visual-studio-tools-for-unity-2-1.aspx?WT.mc_id=dx_MVP4000580

Visual Studio is the new default Unity scripting editor on Windows

 

Announcing the Microsoft Azure Tour

http://azure.microsoft.com/en-us/blog/announcing-the-microsoft-azure-tour/?WT.mc_id=dx_MVP4000580

The Microsoft Azure Tour is a free one day technical training event for developers and IT professionals to help you achieve more success with Azure.

 

Visual Studio Code and Visual Studio Online

http://blogs.msdn.com/b/cdndevs/archive/2015/09/02/visual-studio-code-and-visual-studio-online.aspx?WT.mc_id=dx_MVP4000580

Blog on the integration with Git

 

Excel Function Key Shortcuts

I resisted learning and using keyboard shortcuts for more years than I care to admit. In recent years, however, I have come to appreciate just how valuable keyboard shortcuts can be in Excel and almost any other application you can name. The Blog ExcelTip has just published a detailed article about Function Key Shortcuts that is definitely worth a read if your are interested in expanding your shortcut key horizon.

Technical Education Opportunities

Microsoft AzureCon Virtual Event September 29

Join Live Q&As and interact with the architects and engineers who are building the latest features… 

.Net Native

What .NET Native means for Universal Windows Platform developers

 

Windows 10 Developer’s Guide

The latest Windows 10 developer training contents

Microsoft Edge and Internet Explorer 11

How Microsoft Edge and Internet Explorer 11 on Windows 10 work better together in the Enterprise