Tips

26 posts

How to Map OneDrive as a Network Drive (Windows 10)

OneDrive is a great way to store your files in the cloud. The OneDrive desktop app even allows you to interact with your files stored on OneDrive right in File Explorer on your computer. Doing that creates a OneDrive icon in the notification area of the desktop that keeps the desktop app running and syncing the files online with your computer.

However, some may not want to have the app running, but still have access to their OneDrive files in File Explorer. You can do this by mapping your OneDrive as a network drive. There are a couple of differences using this method compared to the OneDrive desktop app.

The desktop app will allow you to access any shared folders that you have added to your OneDrive, while mapping OneDrive as an network drive will not let you see those folders.

The desktop app will allow you keep your files online only until you need them, freeing up space on your hard drive. When you then open the file, it downloads to your computer, available offline, but using space on your hard drive. Mapping OneDrive as a network drive will keep the files offline and they will only be available if you are online.

Mapping OneDrive as a Network Drive

To map OneDrive, you will need your unique ID. You can get this by signing into the OneDrive website. The ID (or CID) will show at the end of the website address. You will want to select your unique CID from there and copy it.

It will look like this:

https://onedrive.live.com/?id=root&cid=xxxxxxxxxxxxxxxx

What you do next is open File Explorer on your computer, select “This PC” on the left menu, then select Computer from the top menu, and finally select “Map Network Drive”.

This will open the “Map Network Drive” window as below. Select the drive letter that is free and that you want to use for OneDrive when it is mapped as a network drive.

Here is where you want to enter the path, “https://d.docs.live.net/” and then your unique CID value for your OneDrive that you got from the OneDrive website path, as shown above.

After that, you may be required to enter your Microsoft Account username, which is typically an email address and your password. After, this, your OneDrive should now be accessible as a mapped network drive, right in File Explorer.

How to Reset the OneDrive Sync App

OneDrive is a great tool for storing all of your files and photos. A great feature is also how it is integrated into Windows 10. Sometimes, though, the app will stop syncing your computer with your OneDrive in the cloud. This means that any local changes you are making to files aren’t being synced with your online copy. A reset of the OneDrive sync app it is one way to get it back syncing.

The OneDrive sync app (or OneDrive desktop app, which it is also called) creates a folder on your computer that mirrors/syncs with your OneDrive account online. That way, you can access the files directly within Windows Explorer on your computer – making the online cloud storage a part of your computer.

However, sometimes the sync stops working correctly, or the OneDrive cloud icon disappears from the system tray on your Windows 10 computer.

To get everything working correctly, a reset of the OneDrive app usually works.

To do that, just a quick step is required:

  1. Press Windows Key + R on your keyboard to open the run dialog box
  2. Enter this text in the box and then press Enter

%localappdata%\Microsoft\OneDrive\onedrive.exe /reset

This will reset the OneDrive app. This usually takes a couple of minutes. You’ll see the dialog box go away, and the OneDrive cloud icon will disappear from the system tray.

If you see a message that “Windows cannot find…” use this text instead:

C:\Programs Files (x86)\Microsoft OneDrive\onedrive.exe /reset

After a few minutes, the cloud icon should return and the app will start the sync process. This part could take a bit of time, so you’ll need to wait until the status of the OneDrive cloud icon says that it is up to date.

That should reset your OneDrive app and get everything back syncing properly with your OneDrive account online.

Further, detailed information from Microsoft is available here:

Reset OneDrive

Finding the Largest Negative Number in Excel

If you have a list of descending values and want to find the position of the largest negative number, how would you do it?

First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than -3? The answer is -8 is less than -3. So when finding the largest negative number, the number closer to zero is greater than the number further from zero.

To sum it up, -3 is larger than -8. Keeping this in mind will help when trying to find the largest negative number in a list.

A question to ask is: When would you want to find the position of the largest negative number in a descending list?

When Would You Want to Find the Largest Negative Number?

Imagine that you have a starting balance that is reduced each week, and you want a formula to calculate what week the balance ends up being below zero. This would be the week that has the largest negative number. Now this can change – the starting balance could be different; the amount it is reduced each week could be different.

See the example below:

excel largest negative number

So you want to find the week number for the largest negative number. You can see in the example image that this is Week 17.

But now, how would you use a formula to find it automatically?

What Formula Can Find the Largest Negative Number?

The formula would need to ensure that all variables of are handled. The starting balance could change; the amount the balance is reduced each week could vary; the value that you want returned is to the left of the value you are checking. So a standard VLOOKUP wouldn’t work.

How can this be handled? The formula to calculate would include four separate functions – INDEX, MATCH, LARGE, and COUNTIF. And here it is:

=INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

Now that’s a lot of functions to combine, and the formula can be complex at first glance. However, breaking it makes it simpler and easier to understand what’s happening. Lets’s start breaking down the formula.

COUNTIF(B:B,”>=0″)

This function counts the number of numbers in all of column B that are greater than or equal to zero. This seems counter-intuitive since we want the largest number that is less than zero. But hold on…we’ll get to that later.

Using the data above, this function returns the value of 16, since there are 16 numbers in column B that are greater than or equal to zero.

LARGE(B:B,COUNTIF(B:B”>=0″)

This function returns the nth largest number in column B. The nth number that the LARGE function is being told to return is the result of the above COUNT function, which in this example, is 16.

The 16th largest number in column B is 5. Still not the number we are looking for – that comes next.

+1

This takes the number returned from the above COUNTIF function and adds one to it. Why do this?

Remember that the result of the COUNTIF function was 16, and this number was passed to the LARGE function. Without adding 1 to the 16 that is being passed to the LARGE function, the LARGE function would take the list of of descending numbers in column B and find the 16th largest number that is greater than or equal to zero.

That is not what is wanted. What is actually wanted is the first negative number (the greatest negative number).

That is why 1 is added to the 16. Since, in the list of descending numbers, there are 16 numbers greater than or equal to zero, the next largest number would be the largest negative number.

And that is the number that we want! In this example, the 17th largest number is -28.

Now, to get the matching week name. The example shows that the week name is in cell A18 and is Week17. Since the week name is in the column to the left of the searched value in column B, a standard VLOOKUP function won’t work. A combination of INDEX and MATCH will allow the value to the left to be returned.

MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0)

Here the MATCH function takes the result of the functions above, which is -28. It then looks in column B for that number and returns its position. The match type argument is 0. This tells the function that an exact match is wanted. The -28 in column B is in position 18.

Lastly, the resulting 18 is passed to the INDEX function part of the formula.

INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

The index function now looks in the 18th cell of column A and returns that value, which is:

Week17

Here you have seen how to use a formula in Excel to find the largest negative number. While this formula uses four different functions, and does seem complex, it is very flexible.

How to Backup Excel Workbooks [and Save Your Day]

Who hasn’t accidentally saved over their Excel workbook with changes, or was in a hurry to log-off the computer for the day and clicked “Don’t Save” when closing out of Excel before realizing that was the wrong button to click? How many hours of work have you lost, or can you remember what formulas were in your workbook before you mistakenly saved over it? Or do you live in an area where the power often goes out? An Excel backup would be very useful.

These are situations that has probably happened to 95% of Excel users. But, you have File History turned on, right? No? Well, at least your IT department creates nightly backups. But a lot of help that is since you just created the workbook this morning and worked on it all day, so no backup until tonight. Or, what if you are on your home computer with no IT department to manage the backing up of your system?

It’s good that Microsoft has built-in some backup functions in Excel. How do they work? Let’s see.

Excel AutoRecover

AutoRecover is an setting that Microsoft added to Excel. Excel’s built-in AutoRecover options can be accessed in Excel 2016 under File…Options…Save. It defaults to saving the AutoRecover information every 10 minutes, and can be changed to every 1 to 120 minutes.

As well, you have the option of saving the AutoRecover information for a workbook if you close it without saving.

In addition, the location the AutoRecover file is saved to can be changed. Also, you can turn off AutoRecover for each workbook.

The image below shows the relevant information and settings for Excel’s AutoRecover.

excel backup
Excel AutoRecover Options

AutoRecover Timing

If AutoRecover is enabled, the timer starts as soon as Excel is opened. When the set time is reached, Excel checks all open workbooks to determine if any of them have changed. If they have changed, Excel then starts another timer to check if Excel is idle. Every time you make a change to the file, that idle timer is reset.

The idle timer is 30 seconds, but it can be changed via the registry.

There are variations to recover an AutoRecover of a workbook, depending on whether the file was never saved, or if the file was saved at least once. The processes follow below.

File Was Never Saved

If you have never saved the current workbook, and close it without saving, or lose the power to your computer, it could still be possible to recover the file depending on the AutoRecover time settings. If you have the time set to the default 10 minutes and close the workbook without saving before 10 minutes (or even longer depending when Excel was last idle), nothing will be able to be recovered.

To recover the AutoRecover for a file that wasn’t ever saved, go to File…Info…Manage Workbooks…Recover Unsaved Workbooks as shown below.

excel backup autorecovery unsaved workbook
Recovering an AutoRecover of an Unsaved Workbook

File Was Saved at Least Once

If your workbook was saved at least once, and you close it without saving, the process to recover an AutoRecover file is relatively the same.

Go to File…Info…Manage Workbooks and then select the AutoRecover file want to open as shown below.

excel backup autorecover file previously saved workbook
Recovery of an AutoRecover File of a Previously Saved Workbook

Excel AutoSave

A new feature introduce in Excel 2016 and that is only available in Office 2016 with an Office 365 subscription is AutoSave. AutoSave will automatically save your Excel workbook in the background every few seconds as long as it is saved to OneDrive or SharePoint.

Your changes are saved to the cloud, and other uses of the same workbook can see your changes.

excel backup autosave office 365 subscription
AutoSave in Excel 2016 with Office 365 Subscription

To restore a previous version, click on the file name at the top of the Excel window, and then click See all versions. You can then pick which version you want to restore. When you click the version you want to restore, it will open in a new window. If you click the Restore button, the old version window will close and the selected version will become active.

With AutoSave enabled, there is no longer an ability to Save or Save As the file. The only option is Save a Copy.

If you are working on a file that is not saved to OneDrive or SharePoint, AutoSave is disabled for that file. As well, if you are working on a workbook saved in the older Excel file format of .xls, AutoSave will be disabled.

Turning Off AutoSave

The default setting for workbooks saved to OneDrive or SharePoint is to have AutoSave enabled. AutoSave can be turned off, and that setting will stay for that individual file every time you open it. Likewise, if you turn AutoSave back on, it will stay on for that file.

Even if AutoSave is turned off, AutoRecover will still work if it is enabled. Also, if AutoSave is turned on, AutoRecover is off.

AutoSave can’t be turned off as a global setting; it can only be turned off on a workbook by workbook basis.

The Problems With AutoRecover and AutoSave

Having an Excel backup of your workbook is necessary. AutoRecover seems like a good concept – in theory. However, since it relies on waiting for Excel to be idle for 30 once the desired backup time is reached, it could end up never saving a large amount of changes that have been made to a workbook.

As a result, a large amount of your work could be lost if you are busy entering data or formulas, and don’t leave Excel idle long enough to trigger the idle timer.

AutoSave also is a good concept. But it is limited to Excel 2016 that is subscribed to via Office 365. If you have an older version of Excel, or purchased it outright – no AutoSave feature for you.

In addition, AutoSave only works on newer versions of Excel files, and only if they are saved to OneDrive or SharePoint. This does limit those who prefer to save the files to a network location or even on a local hard drive.

Better Backup Free for Excel and Better Backup Pro for Excel

There are two add-in solutions for backing up your Excel workbooks. With the Better Backup Free and Better Backup Pro Excel add-ins, you can ensure that backups of your Excel workbooks are done.

Both versions can automatically save backup copies of your active workbook, and append text and time to your backups. Click here to see a comparison of the two versions.

These add-ins aren’t limited to the number of backups made, won’t delete any backups that are created, and give you flexibility in where and when you backup your Excel workbooks. Check them out.

How to Use the Excel MATCH Function [In Detail]

The MATCH function is part of Excel’s group of lookup functions. This includes INDEX, LOOKUP, VLOOKUP, and HLOOKUP.

Excel’s MATCH function lets you tell it what item to look for in a range, and it then returns the position of that item in the range. Note that it returns, not the item, but the position of the item in the range. This is different from the LOOKUP functions.

Again, it’s pretty simple and basic. However, it’s a powerful function that can be combined with the INDEX function to create very advanced lookup formulas. Here’s how to use it.

 

Start With the Details

Here’s the Excel MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value = the value you want to find. It’s required.

lookup_array = the range of cells to search for the value. It’s required.

[match type] = tells the function how it matches the lookup_value in lookup_array. It’s optional. If you use it, you pass the values of either -1, 0, or 1 to the function.

match_type valueDescription
-1Return the smallest value that is greater than or equal to the lookup_value. The data in lookup_array must be in ascending order.
0Return the first value that is equal to the lookup_value.
1Return the largest value that is less than or equal to the lookup_value. The data in lookup_array must be in descending order.

 

Using the Excel MATCH Function

The sample below will be used as the basis for the examples in this post. Using text for the lookup_value as well as using values for the lookup_value will be shown.

excel match function

Using Text for the Lookup_value

With the above example, enter the following formula:

=MATCH(“Green”, A2:A6).

This will return the result of “3”.

The above formula is looking for the position of the text “Green” in the area of cells A2 to A6. Since the text “Green” is the third cell, the result of “3” is returned.

The match is not case-sensitive, and will return the position, even you enter “0” as the match type.

Also, if you use text for the lookup_value, and also use “0” for the match_type argument, wildcard characters of question mark (?) and asterisk (*) can be used as part of the lookup_value. If you actually want to search for a question mark or asterisk, you will need to type a tilde character (~) before it.

For example, using the formula:

=MATCH(“Ye*”, A2:A6,0),

will return “2” because “Yellow” is in position 2 in the lookup_array range.

Changing the formula to:

=MATCH(“Bl*”, A2:A6,0),

will return “4”. Note in the sample, there are two values in the lookup_array range that contain start with “Bl”. These are both “Blue” in position 4 and “Black” in position 5. Since the “0” match_type argument will return the first result that matches, it returns “4”, since “Blue” is the first match.

This may result in unintended results, so you may have to change the lookup_value criteria to be more specific, depending on your requirements.

Using Values for the Lookup_value

Using the same sample data, enter the following formula:

=MATCH(80, C2:C6).

This will return the result of “4”.

The above formula is looking for the position of the value “80” in the area of cells C2 to C6. Since the value of “80” is the fourth cell, the result of “4” is returned.

Now, change the above formula to this:

=MATCH(79, C2:C6).

This will return the result of “3”.

The above formula is looking for the position of the value “79” in the area of cells C2 to C6. Note that there is no value of “79”.

As there is no entry for the match_type argument, the default match_type of “1” is used. The argument of “1” tells the function to find the largest value that is less than or equal to “79”. In the range above, that value of “50” is in the third cell, so the result of “3” is returned.

Now, change the formula to:

=MATCH(79, C2:C6, 0).

The match_type argument of “0” tells the function to find an exact match. Since there is no exact match, the function returns the error result of “#N/A”.

How about if you enter this formula:

=MATCH(79, C2:C6, -1)?

The match_type argument of “-1” tells the function to find the smallest value that is greater than or equal to the lookup_value. Since the lookup_value is “79”, it would be reasonable to expect the function to return “4”, since the smallest value greater than or equal to “79” in the range specified is “80”, which is in position “4”.

However, the function will return the error result of “#N/A”.

This is because the values in the lookup_array range have to be in descending order if you use the match_type argument of “-1”.

Also, if you use the match_type argument of “1” to get the largest value that is less than or equal to the lookup_value, the lookup_array range values need to be sorted in ascending order.

When to use the MATCH Function?

On its own, there aren’t that many uses for the Excel MATCH function. One use of the function on its own is to use the function to have a formulaic way to verify that a value or text exists in a list.

However, when combined with other functions, MATCH becomes more useful. Such as, when combined with INDEX, it provides a powerful, and flexible replacement for VLOOKUP.

 

Using the (Simple) Excel INDEX Function

When I hear the word “Index”, I tend to think of the dictionary definition. Merriam-Webster defines Index as “a list of items (such as topics or names) treated in a printed work that gives for each item the page number where it may be found”.

For example, an index in a printed book would be a list of topics, typically in alphabetical order, and would show what page the topic would be on.

This is a fitting description for the Excel INDEX function, except that the Excel INDEX function does this in reverse. You tell the INDEX function where to look, and it returns the value in that location.

Using the example of the book from the above paragraph, you would pick a page and then list what topics are found on it.

Excel’s INDEX function lets you lookup a value based on the row and column passed to the function.

Start With the Basics

Here’s Excel’s INDEX function:

INDEX(array, row_num, [column_num])

array = a range of cells or an array. It is required.

row_num = the row in the array to return a value from. This is optionally required. See the note below.

Note: If row_num is not included, column_num must be included.

[column_num] = the column in the array to return a value from. This is also optionally required. See the note below.

Note: If col_num is not included, row_num must be included.

Additional Notes

  • If you include both row_num and column_num, the INDEX function returns the value where the two arguments intersect.
  • If you set row_num or column_num to zero, INDEX returns the value in the entire row or column.
  • If row_num or column_num refer to a location outside of array, the #REF! error will be returned.

How to Use Excel INDEX

using the excel index function

Using the above example, entering the formula:

=INDEX(A2:B6, 1, 2)

The A2:B6 tells the function to look in the range of cells from A2 to B6.

The image below highlights the area that it looks in.

excel index range
Highlighted Excel Index Range

The “1” passed to the function tells the function to look in row one of the range, which is equal to cells A2:B2.

The image below highlights the area that it looks in.

excel index row
HIghlighted Excel Index Row

Next, the “2” passed to the function tells the function to look in column two of the range, which is equal to cells B2:B6.

The image below highlights the area that it looks in.

excel index colunn
Highlighted Excel Index Column

Now, if you look at where the two red rectangles intersect, you will notice that there is one cell. This is the cell that is returned by the function.

The result of this function is the value in cell B6, which is “Large”.

excel index intersection
Highlighted Intersection of Excel Index

That’s how you use the function. Now why would you want to use it?

Why You Want to Use The INDEX Function

Now that how it works is explained, why would you want to use it?

Take a scenario where you have a list of items. You can use the INDEX function to look up a specific item in the list.

That’s about it. There’s not much more to say about this function.

But like so many of Excel’s functions, it can be combined with other functions to create powerful tools. Take, for example, combining the INDEX function with the MATCH function. These two, in combination, are an excellent replacement for the VLOOKUP function.

SUMMARY

Excel INDEX is a pretty straightforward function that does one basic task. It looks up a value based on what row and column is passed to it and returns that value.

It’s true power lies in its ability to be combined with other functions to create very powerful Excel tools.

If you have tips or comments on how you use the INDEX function, please let me know.

Finding the Excel Add-ins Folder Location

Excel Add-ins are used to expand on existing features or to provide new features in Excel. They must be installed and/or enabled to use them. The add-ins are saved in a specific folder.

Use the steps below to find the Excel Add-ins folder location:

1 – Click the Develop tab on the ribbon. If you don’t have the Developer tab, follow these steps to show the Developer tab.

2 – Click the Excel Add-ins button.

excel add-ins

3 – Click the Browse button on the dialog box that opens.

excel add-ins

4 –  Here is the location of the Excel add-ins on the computer. You can now note it, or right click the address bar and copy the address for later use.

excel add-ins

 

 

Adding an Excel Chart Target Line [Detailed Instructions]

Charts are excellent tools to instantly and visually present information. The visual presentation seems to make the numbers a lot easier to understand. They’re great when comparing a target or budget to a forecast or an actual expense.

A target line is usually a straight line on a chart that indicates the target, goal or budget. It allows a quick visual comparison to be made to see if the target is being met or exceeded.

Optionally, a target line can also be variable and not a straight line on the chart. This depends on what data you are using, and what you want to show. For example, if you have several different, separate lines, each with their own budget and actual spend, and you want to show them all together on the chart, you can show a different target for each budget line on the same chart.

Excel 2013 and Excel 2016, makes the process of adding a target line very simple and easy. Combo charts were added in those versions. Here you will see how the combo chart makes the process of adding a target line to a chart very simple.

How to show those types of charts in Excel 2016 will be shown below. Both how to build a chart with a single target line, and how to build a chart with multiple target lines will be illustrated. There will be two different methods shown to create multiple target lines.

This post is a little long with different sections, so here are links to the different sections:

Single Straight Target Line

Assume you have a fixed budget or target for each month of the year, and variable expenses each month. This is quite a typical scenario.  So, a chart with a straight line showing the budget across the chart with columns each month showing what was actually spent is an ideal way to present the data.

Here’s the data that will be used for a straight line target chart. Notice that there are twelve months, the target is the same for those twelve months, and the actual amount changes each month.

sample data for a chart target line
Sample Data for Creating a Target Line

To create a chart with a target line, you simply need to add a combo chart. Very quick and simple so far.

When you insert the combo chart, you are presented with options to decide what type of chart styles you want to combine and what values from the selected data will assigned to each chart style. This is where Excel 2016 makes it very simple.

Here’s how to do it:

Step 1

Click somewhere in the data, or highlight the whole region of data.

Highlight Data for Target Line
HIghlight the Data for the Target Line

Step 2

Click insert Combo Chart under Charts

Insert combo chart for target line
Click Insert Combo Chart

Step 3

Click the Create Custom Combo Chart… option

create custom combo chart for target line
Choose Create Custom Combo Chart

Step 4

Change the target series to a line chart, and change the Actual series to a column chart. If any of the series have Secondary Series checked off, remove the check mark. Both series need to be on the same axis, or the results for this chart will not be correct.

select chart types for target line
Select Chart Types for Different Series

Step 5

Sit back and enjoy your new chart!

The target now shows as a line on the chart, and the actual expenses for each month can now be easily visually compared to the target.

chart showing target line
End Result Chart with Target Line

Multiple Target Lines

The previous example showed how to show a single, fixed target or budget line. This works great when looking at a single line of expenses with a fixed budget. However, what if you had a few separate lines each with their own budget or target that you wanted to compare on one chart?

Looking at the sample data shown below, you can see that there are eight different projects, each with their own budget and actual expenditures.

data for multiple target lines
Sample Data for Multiple Target Lines Chart

The process to show multiple target lines is quite similar to the process to show a single target line, as demonstrated above.

There are two different ways to show the budget or target line when showing multiple lines on a chart. You can use markers or you can use error bars. First the use of line markers will be shown and then later, error bars will be used.

Line Markers

Here’s how to do it:

Step 1

Click somewhere in the data, or highlight the whole region of data.

sample data multiple target line
Sample Data for Creating Multiple Target Lines

Step 2

Click insert Combo Chart under Charts

insert combo chart for target line
Click Insert Combo Chart

Step 3

Click the Create Custom Combo Chart… option

create custom combo chart for target line
Choose Create Custom Combo Chart

Step 4

Change the Budget series to a Scatter chart, and change the Actuals series to a column chart. If any of the series have Secondary Series checked off, remove the check mark. Both series need to be on the same axis, or the results for this chart will not be correct.

select scatter and clustered column for multiple target line
Select Chart Types for Scatter and Clustered Column

Step 5

Sit back and enjoy your new chart!

The budgets for each project now show as dots on the chart, and the actual expenses for each project can now be easily visually compared to the budget.

chart with budget target line
End Result Chart With Multiple Budget Lines

Changing the Marker Type

The marker dots for the budget can be changed to a variety of shapes. To do that, right click one of the marker dots, and then select Format Data Point. After selecting Fill & Line, Click Marker, and then click the arrow beside Marker Options. Make sure the radio button beside Built-in is selected, and then select the type of marker you want. You can also change the size of the marker. The image below shows the available markers that you can choose from and how to select them.

marker lines for target lines chart
Available Marker Lines

Below is the same chart shown above, but with a straight line chosen as the marker, and the size of the marker increased to 14.

budget markers changed target lines
Budget Markers Changed to Lines

Multiple Target Lines Using Error Bars

Depending on the data that you use for the charts or the number of different separated budget and expense combinations that you want to show, line markers may not be the best way to go. Using line markers to show the target could end up making the difference between the budget and actual amounts hard to see since the markers could end up begin very thick. This is due to increasing the size of the marker to make it as wide as or wider than the column also increases it height.

A way to adjust the budget section of the chart to be thinner is to use error bars instead of the markers. This creates a line on the chart for each budget rather than the thick markers. The line for the error bars can be changed for its color, size and much more.

Here’s the error bar method:

Use the exact same process of steps 1 through 5 as outlined above for using line markers and then continue on doing this:

Step 6

Select the series for the budget line by clicking one of the markers. Make sure the whole series is selected. Click Design under Chart Tools on the ribbon. Click Add Chart Element under Chart Layouts on the ribbon. Move the selector over Error Bars. The image below shows this.

adding error bars for target line
Adding Error Bars to the Chart

Step 7

For the data and chart used in this example, both horizontal and vertical error bars were automatically added and the vertical error bars were selected. You only want horizontal error bars, so remove the vertical error bars. To do this, select the vertical error bars, then select Fill & Line and the click the radio button beside No Line. The image below illustrates this.

removing vertical error bar for target line
Removing the vertical error bars

Step 8

The line markers now need to be removed. To do this, select the marker lines. You may need to click out of the chart and then select the marker series. Once selected, change the Marker Options to None, as shown below.

remove line marker for target line
Remove the Line Marker

Step 9

Now the horizontal series of the error bars can be formatted. Select the horizontal error bar series. Change the End Style to No Cap, and change the Error Amount to Custom. Click the Specify Value box and in the pop-up form, change both Positive Error Value and Negative Error Value to .4. The changes are illustrated below.

horizontal error bars for target line
Settings for the Horizontal Error Bars

And here is the result of all that work.

Error bars showing budget for target line
Excel Chart with Error Bars for Budget

Notice, though, that there is no legend showing what represents the budget. This is because the marker was changed to None in step 8 above. A workaround is to draw a line identical to what you set for for horizontal error bar.

Here is the same chart after adding a line as a drawing and moving it onto the chart legend. If the chart is resized or moved, the drawn line for the legend will also need to be resized or moved.

drawn line on chart legend for error bar target line
Chart with a Drawn Line on Legend for Budget

The error bars can be changed in color, size, transparency and dash type. This makes them very flexible and versatile.

Summary

Budget or target lines on chart are great for visually showing the target and whether or not the target is actually be achieved. There are different ways of presenting the target on charts, and can be quite a bit of flexibility available in Excel for changing the charts to present what you want.

If you have any tips or questions, please feel free to comment below.

Live Transpose in Excel With an Array Formula

Excel’s transpose feature is great for changing data in a vertical format to a horizontal format, and from a horizontal format to a vertical format. Excel’s transpose, though, creates a static copy of the original data. If the original data changes, the transposed copy does not change.

This may be your desired result, but if you want the transposed copy of the data to update at the same time as the original data changes, there is a way create a transposed copy that changes along with the original data. This will create a “live” transposed copy.

The way to do this is by using an array formula. Check here if you want more information on array formulas. As a quick note, you do need to use CTRL + SHIFT + ENTER when entering an array formula for it to work.

Live Transpose Example

To illustrate how a live transpose in Excel works, take a look at the sample image below. It shows the original table of data that will be the source for the live transpose copy.

excel live transpose source data

Once you decide where you want the live transposed copy of the original data to be placed, you just need to highlight a range of cells that is equal to the transposed version of the original data. Note that it is the opposite size of the original data, since it is a transposed copy.

Therefore, In this example, since the original data is 14 columns wide by 9 rows high,when the data is transposed, its shape will change to be 9 columns wide by 14 columns high.

So you need to select a range that is 9 columns wide by 14 columns high.

highlight a range for live tranpose

Once you have highlighted the range where you want the live transposed copy of the data to be located, start to type the TRANSPOSE formula. You can either click in the formula bar first, or just start typing the TRANSPOSE formula. In the example here, it would be:

=TRANSPOSE(A1:N9)

And when you type CTRL + SHIFT + ENTER after typing it, it turns into this (NOTE the curly braces that were automatically added):

{=TRANSPOSE(A1:N9)}

And this is the resulting view:

a live transpose in Excel

Anywhere you click in the range, the formula in the formula bar does not change. It stays the same.

Now, if you change any values in the original data, the transposed version will automatically update to the new value you have entered.

Summary

Hopefully, this post has shown you how to use an array formula version of the TRANSPOSE function in Excel to create a “live” transposed version of the original data.

Let me know what you think, or if you have any tips or comments about this method.

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function is a little known function. However it has a very fitting name because it multiplies and then returns the sum of the groups of multiplications, or creates a SUM of the PRODUCT.

Starting With The SUMPRODUCT Function

SUMPRODUCT(array1, [array2], [array3], )

  • array1 = the first array that you want to multiply and add together. This is required
  • array2, array3, … = the next 2 to 255 arrays of numbers to multiply and add together. This is optional

A few notes about the function:

  1. If you have more than one array of numbers and they are not the same size, the function will return the #VALUE! error
  2. If any arrays do not have numbers, they will be treated as a zero by the function
  3. If a single array is passed to the function, it will only sum. It will not multiply.

How to Use It

In the image below, sample data and four examples of the SUMPRODUCT function are shown.

Notice that both cells A3 and A4 have a single array in their functions, while cells A5 and A6 have two arrays in theirs.

sumproduct

What do you think the result will be of each of the different functions?

The first function in cell A3 is using only two cells in the SUMPRODUCT function, specifically A1 and B1. So will it multiply the 1 (cell A1) * 2 (cell B1) and return 2, or will it add the two value and return 3?

What about the function in cell A4 that is using four cells in its SUMPRODUCT function? Will multiply the four cells or add them?

And then, what about the functions in cells A5 and A6? What will they return?

Here you can see what the results are.

sumproduct

Is it what you expected? If not, hopefully the explanation below will help you work through the formulas to see how the results were achieved.

Notice that when the SUMPRODUCT function is only passed one array of numbers, it does not multiply the numbers, but it adds them.

The function in cell A3 is passed the values in cell A1 of 1 and cell B1 of 2, so it is adding the 1 and 2 to equal 3.

The function in cell A4 is passed the values in cells A1 through A4 of 1,2,3 and 4. It is adding the four amounts to equal 10.

However, the functions in both cells A5 and A6 are both being passed two arrays, so the function is multiplying and then summing the product of the multiplication.

But how is the function returning the answers of 11 in cell A5 and 94 in cell A6?

Remember cell A5 has the formula of

=SUMPRODUCT(A1:B1,C1:D1).

You may think that it would multiply A1 * B1, multiply C1 * D1, and then add those two results together. Actually what the function is doing is multiplying A1 * C1, and B1 * D1, and then adding those two results together. This results in (1*3) + (2*4), which equals 11.

And what about the function in cell A6? Notice how it also refers to two arrays, the same as the formula in cell A5. The difference is that the arrays in cell A6 are not just referring to one row of data, but to two rows instead.

So the function in cell A6 returns 96, which is the result of (1*3) + (2*4) + (5*7) + (6*8).

To summarize how the function works, if there is more than one array being passed to it is this:

Take the first value in each array and multiply them. Then do the same for each next value in the arrays. Then add them together.

Now you fully understand how to use the SUMPRODUCT function.

But the really big question is, Where is this function useful?

What to Do With SUMPRODUCT

One SUMPRODUCT use can be for creating summaries of data, totalling based on criteria. For example, if you have data showing sales for different locations and colors and want a total of each location, or a total of each color, or a total of each unique combination of location and color, you can use SUMPRODUCT.

See the image below.

sumproduct

In the above image, notice that there are several entries for each location, each color, and also for the unique combination of location and color. For example, “North” is found on six rows in column A, and “Red” is found on seven rows in column B. The combination of “North” and “Red” on the same row is found on four rows.

Now, in typical Excel fashion, there are several ways that you could create those totals. You could make the data into a table, add totals to it, and then filter based on what you needed. You could use SUMIF or SUMIFS. And there are probably other ways that you do it. Let me know in the comments if you know of other ways to create summaries.

What will be considered here, is how to use SUMPRODUCT to create summaries of the data. Here is a summary of the above data based on location.

sumproduct

Here’s a summary of the data based on color.

sumproduct

And finally, here is a summary of the data based on a combination of location and color.

sumproduct

Each of these three summaries of the data are using the SUMPRODUCT function to filter the data and add the amounts based on the criteria.

Here are the same summaries, but showing the actual SUMPRODUCT function.

sumproduct

sumproduct

sumproduct

Did you notice how the SUMPRODUCT is a bit different. For example, one of the SUMPRODUCT function formulas is:

=SUMPRODUCT((E2:E26)*(A2:A26=H2)).

The cell H2 contains the word “North”. An alternate to the above formula could be

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)).

So you can type in criteria, or use cell references as the criteria.

The first array is the column that you want to sum, the second array is the column that you want to filter on. This formula will filter on one criteria. To filter on more than one criteria, you just need to add more arrays and the filter for each one.

For example, the formula

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)*(B2:B26=”Red”))

will add the amounts in column E that have “North” in column A and “Red” in column B.

Summary

SUMPRODUCT is not a commonly used function, but it can be used to multiple and add arrays of numbers. A probably surprising use of SUMPRODUCT is to create filtered subtotals of numbers.

If you have of other ways to use SUMPRODUCT let me know in the comments below.