Copy Code to Your Excel Workbook

You might find Excel code on this blog, or my Pivot Table blog, and want to copy it into your own workbooks.

I’ve updated my web page, Adding Code to an Excel Workbook, to show instructions for Excel 2010.

New Video

There’s a new video on that page too, that shows how to

  • copy Excel code from the internet
  • insert a code module in Excel
  • paste the code into the module
  • run the new macro
  • save the file as macro enabled

Video: Copy Excel VBA Code

You can watch the new video here too, if you’d like to see the steps.

In the video, the sample pivot table code was copied from the Excel Pivot Tables blog: Change All Pivot Table Value Fields to SUM

NOTE: For the Excel 2003 version of the Copy Code tutorial, please visit: Copy Code to Excel 2003 Workbook

Modify the Code

It’s not always so simple to copy Excel code, and use it in your workbooks. You might have to modify the code, by changing worksheet names, or cell references, to match what is in your file.

For example, if you copy my code for multiple selections from an Excel drop down, you might have your data validation cells in a different column. You could change the column number in the code, as shown in the following video.

Note: This code goes on a worksheet module, instead of a regular module.

_________________

Store Multiple Values With Excel Scenarios

If you’re working on the office budget, you might be collecting data from different departments.

Or maybe you’re planning your back to school spending.

  • If you get bonus at the end of your summer job, you can use some higher budget numbers.
  • If the bonus falls through, you’ll need your low budget estimates.

Store Data With Scenarios

With Excel Scenarios, you can store different sets of values (in up to 32 cells) in a workbook. Then, without any programming, you can switch between the saved values.

In this example, we store budget projections for two departments. View and print one department’s budget, and then switch to the other department, all in the same cells.

scenarios05a

Download the Sample File

To see the written instructions for creating scenarios, please visit the Excel Scenarios page on the Contextures website.

There is a sample file that you can download, to see how the Scenarios work.

Watch the Excel Scenarios Video

To see the steps for creating and showing scenarios, you can watch this short video tutorial. I also share tips for quickly naming cells, and automatically adding those names to your formulas.

sure to date stamp it, or delete it before saving the workbook. You don’t want to keep potentially misleading data in your files.

Scenario Summary Formatting

And the Scenario Summary formatting is about as ugly as Excel gets – purple and grey.

Maybe they’ll improve that in the next version! But don’t hold your breath — I doubt that it will ever be changed.

scenarios12b

Watch the Video

To see the steps for creating Scenario Summary, please watch this short video tutorial.

This video includes a tip for adding the Scenario command in the Excel 2010 Ribbon, so it is easy to switch between Scenarios.

More Scenario Tutorials

Here are links to 3 Scenario tutorials, on my Contextures site:

Create and Show Scenarios – With Scenarios in Excel, you can store multiple versions of data, in the same cells

Create Scenario Summaries – After you create 2 or more different Scenarios in Excel, use a Scenario Summary to show an overview of the data. This is a static report that is designed to show the Scenario data at a moment in time.

Automatically Show Scenarios – see how to use a macro to automatically show a Scenario, when you select its name from a drop down list on the worksheet

Scenarios Programming – Use these macros to create a list all the scenarios, or create new scenarios, or update the values for existing scenarios, from a list on the worksheet

____________________

Excel Dashboard High and Low Values

My clients sometimes ask for help with building Excel dashboards, so they can present a summary of their data to their customers and co-workers.

In a dashboard, you want to make the best use of limited space, and only show key information. For example, instead of showing all the sales data, you can show just the highest and lowest values.

MIN and MAX Functions

It’s easy to pull the top and bottom values from a list, by using the MIN and MAX functions. It’s a little trickier though, if you want to show the high and low amounts for a specific product in a long list.

In this example, I want to calculate the MIN and MAX for each product, then put that information on the dashboard

Create a MIN IF or MAX IF formula

There’s no built-in MINIF or MAXIF function, but you can use MIN or MAX with the IF function, to create your own. The steps are shown in the video, at the end of this article.

First, to get the minimum quantity sold for File Folders, the array formula in cell D8 is:

=MIN(IF($G$2:$G$17=C8,$H$2:$H$17))

After you type the formula, press Ctrl + Shift + Enter, so it is array entered.
The same technique is used in cell E8, with MAX, instead of MIN:

=MAX(IF($G$2:$G$17=C8,$H$2:$H$17))

minmaxminifmaxif01

Excel Dashboard Course

If you’d like to add dashboard skills to your Excel tool kit, I recommend the upcoming Excel Dashboard Course offered by Mynda Treacy from My Online training Hub. Mynda is an accountant, and her dashboards focus on the numbers, not the fluff.

There are 9 sessions in the course, with video tutorials that are short and to the point. They cover the key steps and features, and you can practise the techniques in the sample files. Replay the videos as often as you need, for up to 12 months. The course includes 6 weeks of support from Mynda, so you can post questions, read comments, and ask her to review your completed dashboard.

This course is not for Excel beginners, because the fast pace could be overwhelming. Lots of material is covered, very quickly. And, if you’re already a dashboard expert, you won’t need this course. It’s designed for Excel users who are beyond the basics, and who enjoy learning by seeing a demo, then practising the new skills.

You can see the course details and a sample video here: Excel Dashboard Course

Registration is only open for two weeks, until August 14th, so don’t wait!

Watch the Min and Max Video

To see the steps for creating MIN, MAX, MIN IF and MAX IF formulas, please watch this short video tutorial.

_______________________________

Control Budget With Excel Data Validation

If you’re building a budget in Excel, you can limit the total amount that is entered, to help prevent anyone from going over budget.

I shared this tip a few years ago, in this blog post: Limit the Total Amount Entered in Excel

data validation custom error message - over limit
data validation custom error message – over limit

New Features

In the new version, I’ve added a few more features, to help you fill in the correct amounts.

  • Below the Budget Limit, in cell D3, you can see the amount that hasn’t been added to the budget yet.
  • In column D, you can see the maximum amount that can be entered in each row, based on the entries in other rows. This makes it easier to adjust individual items, while you finalize the budget.
data validation in cell D2 limits budget amount
data validation in cell D2 limits budget amount

And remember, data validation isn’t foolproof, so you’ll still have to check those budgets, to make sure nobody is trying to get a little extra!

Download the Sample File

To see the formulas, and test the data validation, you can download the sample budget from my Contextures website.

Go to the Sample Excel Files page, and in the Data Validation Section, look for DV0058 – Limit Budget Entries with Data Validation.

Watch the Budget Limits Video

To see the steps for setting up the data validation and formulas, to set the budget limits, you can watch this short video tutorial.

_______________________________________

Line Between Dates in Filtered List

A while ago, I showed you how to add a red border at the top of a row, when a new date starts in a list.

You can read those instructions here: Separate Excel List with Automatic Lines.

Line Between Dates in Filtered List
Line Between Dates in Filtered List

Conditional Formatting Problem

And if you’re using this technique, remember the painful lesson that I learned – don’t copy and paste within a table that has conditional formatting.

You could end up with an Excel 2010 Conditional Formatting Nightmare. And nobody wants that!

conditional formatting duplicate rules problem
conditional formatting duplicate rules problem

Lines Disappear in Filtered Lists

Recently, I heard from Lon, who liked the tip about red line borders. But Lon noticed a problem – the line didn’t always show if the list was filtered.

For example, if we filter the Product column, to hide Paper, the red borders for some of the dates disappear.

Lines Disappear in Filtered Lists
Lines Disappear in Filtered Lists

I hadn’t noticed the problem, because my list is usually filtered by date, to show only the latest month’s data. Since the conditional formatting is based on the date column, it would continue to work correctly.

But we can change the conditional formatting, so it works in a filtered list.

Change the Formula for Filtered Lists

To make the conditional formatting work in a filtered list, we can’t use the original formula, which was

=$A1<>$A2

That formula just compares each date to the date above it, and doesn’t care if the rows are hidden or visible.

Instead, we’ll use a formula that was created by Laurent Longre. It lets you work with visible rows after a filter. For information on this formulas, read the Power Formula Technique section, in this article at John Walkenbach’s web site: Excel Experts E-letter

Here is the much longer formula that we can use, to compare dates in the visible rows only.

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$1:$A2,ROW($A$1:$A2)
-MIN(ROW($A$1:$A2)),,1)), –($A$1:$A2=$A2))=1

Note that there are two minus signs in front of the last open bracket – it’s not a long dash.

ConditionalFormatFiltered03

Date Separator Lines Show When Filtered

With the new formula, the red lines separate the dates, even if the list is filtered. In the screen shot below, the Product column is filtered to hide Paper, but the date line for July 18th shows up.

Date Separator Lines Show When Filtered
Date Separator Lines Show When Filtered

Download the Sample File

There is another example of this type of formula on my Contextures website – Count Visible Items in a Filtered List.

And you can download the sample file used in this blog from the Contextures Sample Excel Files page. In the Conditional Formatting section, look for CF0004 – Conditional Formatting in Filtered List. The zipped file is in Excel 2007/2010 format, and contains no macros.

_________________

Excel Books for Beginner to Intermediate

excel2010_missingmanual Stephan emailed me recently, and asked what books I’d recommend for an advanced beginner and for an intermediate user.

On the Contextures website, I’ve got a list of Excel books. They range from books for absolute beginners, to specialized books on statistics and financial modelling.

Book Suggestions

Here are the advanced beginner books that I suggested to Stephan – do you agree with these choices?

You might find these at your local library, or a nearby bookstore

  • Excel 2010: The Missing Manual; Matthew
  • MacDonald; ISBN: 1449382355; 898 pages; 2010; US$39.99
  • Microsoft Excel 2010 Step by Step; Curtis Frye; ISBN: 0735626944; 480 pages; 2010; US$29.99
  • Slaying Excel Dragons: A Beginners Guide to Conquering Excel’s Frustrations and Making Excel Fun; Mike Girvin, Bill Jelen; Holy Macro! Books; ISBN: 978-1615470006; 532 pages; 2011; US$29.95
  • Excel 2010 All-in-One For Dummies; Greg Harvey; ISBN: 0470489596; 792 pages; 2010; US$

Check the Contents

If you’re book shopping online, before you buy an Excel book on Amazon, use the “Look Inside” feature, if available, to see what the writing style is like, and check the table of contents.

The quick peek will give you a general overview of the book, and could help you decide if the book is right for you.

image

Read the Reviews

The customer reviews can be helpful too – both the negative and positive ones. Sometimes another person doesn’t like a book because it’s not for absolute beginners, and that might be a positive thing for you

. And there’s always the possibility that some of the glowing reviews were written by the author’s mother or friends! 😉

image

If you can get to a bookstore, you can flip through the Excel books there, and head home with a few that you like.

It sometimes costs a bit more than shopping online, but it’s worth it, to find the books that are best suited to your needs and learning style.

And it’s good to support your local businesses!

__________

Check Multiple Criteria with Excel INDEX and MATCH

Use INDEX and MATCH together, for a powerful lookup formula. It’s similar to a VLOOKUP formula, but more flexible — the item that you’re looking for doesn’t have to be in the first column at the left. Watch the video to see how it works (there are written instructions too), and download the sample workbook to follow along.
Continue reading “Check Multiple Criteria with Excel INDEX and MATCH”