Saturday, December 31, 2011

Data’s got a brand new bag: PowerPivot for Excel (video)

I liked the heads up but was expecting a little more from the video. For instance a quick example how you linked up your sheets in the way you showed.

Still, thanks for the heads up but my data amounts are hardly enough to expand on it like this. But having said that, a small side-step if I may...  Since beginning this week I started using Outlook 2010 (was using SeaMonkey's mail). Part of this decision was triggered by the 'Business Contact Management' extension; that critter is outrageous for small businesses like I represent.

I'm slowly, but steadily, starting to become -very- impressed with the way you can inter-exchange data between Office applications. A bit comparable like you showed here; one sheet easily connected to the other. In my case; accessing Outlook data (contacts) from Word (using VBA obviously).

Alas, thanks for sharing but..  - no offense - but I am looking forward to one of your "casual" example video's. Those really rock, I'm even actively (carefully) spreading them with some of my customers :-)

View the original article here

Remembering Nathan (Nate) Oliver

thank you for your tribute to Nate

I am so sad that I will not see Nate or talk to him again.  He was gifted in many ways.  His brilliant mind and physical skills were tempered by his passion for people and being helpful.  I love what everybody has written, even those who are just starting to learn who Nate was through his legacy of posts, blogs, pictures, and more.  I took many pictures of Nate over the years.  They are here:

Goodbye my friend and colleague.  We had great times and I always learned something from you.  Even though you are gone, your code lives on,  thank you

To Nate's family and friends, I give my most sincere condolences

Warm Regards,


Microsoft MVP, Access

View the original article here

Friday, December 30, 2011

Tracking small projects in Excel

Hi Anneliese,

If you ask any project manager what's the worst thing that he can do to manage his project's, he'll tell you "using Excel to schedule and manage the project".

MS Project is way better at that and Excel is really not made to manage projects. Problems with Excel will start one you have change requests, how can you handle them?

I'm a contributor to PM Hut and one of the common things that project managers tell us is how Excel "ruined" the project (of course, you might think that they're blaming the tool, but when you start hearing the same thing over and over again, you will start wondering).

PS: Glenna's work is perfect, and I'm not criticizing it in any way (as she tried her best to make Excel work as a PM software), but again, and in my opinion (and the opinion of many other project managers), Excel is just not made for project management.

View the original article here

Thursday, December 29, 2011

Introduction to Spreadsheet Risk Management

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit

Part 1: An Introduction to managing spreadsheet riskPart 2: How companies can manage their spreadsheet riskPart 3: Excel’s auditing functionsPart 4: Using external software packages to manage your spreadsheet risk

Introduction to Spreadsheet Risk Management

The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive.

See the full news article here:

Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.

Because we all love Excel, right? True certainly, but the main reason is that it is intuitive, flexible, cost effective and provides quick solutions to high priority day to day problems.

And what is the alternative? The IT department. The simple fact is that end user developed spreadsheets often fill the gap between the current business requirements and formally managed IT systems.

Unfortunately this reliance on spreadsheets, rather than robust, well governed IT solutions can add significant risk to an organisation if it is not properly managed.

Spreadsheet risk is the risk that a business could lose revenue and profit, fail to comply with regulators or find its reputation damaged as a result of spreadsheet error (be it fraudulent or unintentional).

Poorly structured spreadsheets can also lead to a loss of productivity and increased audit costs, further damaging the bottom line.

A recent study of typical enterprise spreadsheets by the Tuck School of Business at Dartmouth found that 94% of spreadsheets and 5% of all formulae within spreadsheets contain errors.

The European Spreadsheet Risk Interest Group (EuSPrIG) are the voice of best practice spreadsheet development and the management of spreadsheet risk.

Below are a couple of examples of what can go wrong from the EuSPrIG website:

C&C Group admit to mistake in revenue results

Shares in C&C fell 15 per cent after it said total revenue in the four months to end-June had not risen 3 per cent as reported, but had dropped 5 per cent. C&C said cider revenues in the UK had fallen 12 per cent, not 1 per cent, while cider revenues in Ireland were flat instead of up 7 per cent as reported last week.

C&C’s group finance director and COO said the error in last week’s announcement occurred after data were incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement. “It was basically human error… there’s nothing wrong with our accounting systems,”

FSA fines Credit Suisse £5.6m

The FSA decided to impose a financial penalty of £5.6 million on the UK operations of Credit Suisse in respect of a breach of Principles 2 and 3 of the FSA’s Principles for Business:

Principle 2 states that “A firm must conduct its business with due skill, care and diligence.”Whilst Principle 3 requires that “A firm must take reasonable care to organise and control its affairs responsibly and effectively, with adequate risk management systems.”

More specifically, section “2.33.3. The booking structure relied upon by the UK operations of Credit Suisse for the CDO trading business was complex and overly reliant on large spreadsheets with multiple entries. This resulted in a lack of transparency and inhibited the effective supervision, risk management and control of the SCG {Structured Credit Group}”

Excel is, and is likely to remain, the first choice for businesses when developing financial models and analysing data. The risk that this introduces to businesses if unmanaged is real and potentially material.

In the next article we will look at ways that companies can manage their spreadsheet risk.

In my brief usage of Excel, I have experienced several risky situations. Sometimes it just a mild data loss, other times, there was a potential of revenue loss or customer annoyance. Due to the economic slowdown many large and small corporations are employing spreadsheet based solutions. And if you do not understand the risk & manage it, then your risk being featured on EuSPrIG’s horror stories page.

Do you know (or experienced) a spreadsheet horror story? Please share your ideas and best practices with us using comments.

Many thanks to Myles for writing this series. Your experience in this area is invaluable. I am really keen to learn about the best practices and adopt them in my business. If you enjoy this series, drop a note of thanks to Myles thru comments. You can also reach him at Excel Audit or his linkedin profile.

RSS feed for comments on this post. TrackBack URI

View the original article here

Wednesday, December 28, 2011

Formula Forensics No. 005 – Zebras and Checker-Boards

This week in Formula Forensics we’ll look at, Zebra Stripes and Checker-board Conditional Formatting.

This idea is inspired by a number of posts over the past few years asking about zebra stripes but specifically BobR who in in June 2011, also asked about Checkerboards in the post: Want to be an excel conditional-formatting Rock Star, Comment No. 154.

I got the conditional format for alternating row and column colors,

Is there a conditional format to make it a checkerboard whereas the cell A2 will remove either the conditional for the row or column and then alternately to A4, B1, B3 etc?

Chandoo responded fairly quickly with this Conditional Formatting formula:


Unbeknownst to Chandoo I posted this about a minute later:


Both formula correctly answer BobR’s question.

So today we’re going to pull apart Zebra Stripes and Checker Boards and see what makes them tick.

As always you can follow along in a download file here: Download File.

Zebra Stripes as Conditional Formatting is simply applied using a simple formula within Conditional Formatting.


Conditional Formatting requires a formula that returns a boolean “True” to apply a format or a Boolean “False” to not Apply a format.

So the formula is better read as: If MOD(ROW(),2)=0

And  If MOD(ROW(),2)=0, the formula will evaluate as True

This is best evaluated as 3 columns on a worksheet.

In cells

B5:B10 The formula =Row() returns the Row Number

C5:C10 The formula =Mod(Row() ,2) returns the Mod of Row Number, divided by 2

The Mod function returns the remainder of the division of the Row Number divided by 2,

So in Row 5, Mod(Row(),2) = Mod(5, 2) = 5/2 = 2 Remainder 1 = 1

and in Row 6, Mod(Row(),2) = Mod(6, 2) = 6/2 = 3 Remainder 0 = 0

D5:D10 The formula =Mod(Row() ,2)=0 checks the remainder against the value 0

This is what evaluates to either True or False depending on the Row number.

Where the Values are True the Format will be applied (Even Rows)

The Conditional Formatting can be applied to Odd Rows If the Formula is slightly altered

=Mod(Row() ,2)=1

Similarly the formatting can be applied to Columns using


RobR received two responses to his Checker-Board Conditional Formatting request.




Lest see what’s inside these two formula.

This is a simple If Formula with 3 components


If Condition        MOD(ROW(),2)=1

Value if True:     MOD((ROW()-1)*8+COLUMN(),2)=0

Value if False:    MOD((ROW()-1)*8+COLUMN(),2)=1

The If Condition is already known to us, as it’s the same formula used in the Zebra Stripes above.

It evaluates to True when it is on an Odd Row.

So when it is an Odd numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=0

And when it is an Even numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=1

We can notice that these are the same formulas which have a different ending of =0 and =1


This section Takes each Row subtracts 1 and then multiplies this number by 8. This can be expressed as simply as saying multiply the Row * 8.

This will always return an Even Number and could have been simplified to Row()*2


The next bit adds the column number to the previous Even Number.

So now this part will be Odd when the column is Odd and Even when the column is Even.


The remainder of the formula is the same as the Zebra Stripes formula.

An Odd Number (Odd Columns) in the section above will return a 1 as the result of =Mod(Odd,2)

An Even Number (Even Columns) in the section above will return a 0 as the result of =Mod(Odd,2)

When evaluated against 0 will return True for Even Columns and False for Odd Columns.

Now the exact same happens in the False section of the If formula except that it is evaluated against 1.

I tackled this problem from a different direction to Chandoo.

Knowing that Even + Even = Even and Even + Odd = Odd and that the row and Column Numbers increase in each direction by 1 each Row/Column, it was simply a matter of adding the Row and Column numbers together and checking if it was Odd or Even

The Excel function IsOdd() and IsEven() both return a Boolean “True” if the contents are Odd or “Even” respectively. This negates an external truth check as described above.

This is easily shown by adding a formula to the Checker area


Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=1

If the alternate shading is required a switch to


Does the trick.

Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=0



You can download a copy of the above file and follow along, Download Here.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensics 001 – Tarun’s Problem

Formula Forensics 002 – Joyce’s Question

Formula Forensics 003 – Lukes Reward

Formula Forensics 004 – Freds Problem

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or this post.

If you have a formula that you don’t understand and would like explained but don’t want to write a post also send it in to Chandoo or Hui.

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: Check-Board, column(), if(), iseven(), IsOdd(), Learn Excel, Microsoft Excel Conditional Formatting, Microsoft Excel Formulas, MOD(), row(), Zebra Stripes

RSS feed for comments on this post. TrackBack URI

View the original article here

Excel mashup tutorial

One of our Excel MVPs—Jan Karel Pieterse—emailed us earlier this week suggesting we take a look at the online tutorial he created about Excel Mashups. We did, and now we want to share it with you. 

In case you missed it, we recently blogged about, a new site for developers which includes demo apps and code snippets for building Excel “mashups.”  If you don’t know exactly what a mashup is, you’re not alone—it’s just a web page that takes data from existing sources and combines it into something new. For example, a web guide to restaurants that consists of search results, Bing maps, and customer reviews.

You create an Excel mashup by uploading a workbook to SkyDrive and then embedding it on a web page. Then, you use JavaScript to programmatically interact with that workbook.

Jan Karel's tutorial:

Shows you how to embed a workbook on a web page.Provides JavaScript code snippets.Walks you through creating a web control.Provides a demo that pulls it all together.

And, you can read his instructions in Dutch or English! Have fun!

View the original article here

Tuesday, December 27, 2011

What is one area of Excel you want to learn more? [Survey]

AppId is over the quota
AppId is over the quota
Posted on December 2nd, 2011 in Learn Excel - 190 comments

It is almost weekend. Today we (Jo and I) are going to watch a cricket match being played in Vizag. We are pretty excited as this is the first time we are watching a match in stadium.

So, let keep this light and fun. I want to know What is one area of Excel you want to learn more?

What areas of Excel you want to learn?

I will go first. I want to learn more about Data Tables & Simulation.

What about you? Go ahead and tell us using comments.

Note: Here are a few choices if do not know what else is out there.

FormulasArray FormulasFormattingConditional FormattingChartingAdvanced ChartingPivot Tables & ChartsTablesData TablesValidationFilters & SortingVBA (Macros)Linking to Databases etc.SolverStatistical Analysis (regression, time series etc.)Scenarios, What if analysisDashboards

So go ahead and tell us.

RSS feed for comments on this post. TrackBack URI

View the original article here

Monday, December 26, 2011

Create smashing mashups with Excel

Today's post is brought to you by Larry Waldman, a program manager on the Excel team. You may remember Larry from his previous posts on embedding Excel files on web sites and designing Excel solutions for the Web.

Hello, web developers! Today, I want to point you to, a new site from the Excel team that is designed to help you get started creating spreadsheet-based mashups. To create an Excel mashup, you use Excel Web App to create an embedded workbook that is stored on SkyDrive, and then displayed in a host web page. Once you've embedded the workbook, you use the JavaScript API to programmatically interact with it. 

To get started, do the following three things:

Sign up for a free SkyDrive account, if you don't already have one.Upload your Excel file to SkyDrive.Grab the JavaScript embed code snippet and start coding!

We've created some example mashups to help you see what's possible, including a Daily Calories Calculator and a Destination Explorer.

In this example, you search for a specific food, choose a serving size, and then see dietary information appear inside cells in an embedded workbook. This interactivity is accomplished by using the JavaScript API. This example also shows how to use the calculation and charting features of Excel Web App to render a chart that compares daily calorie intake to recommended dietary guidelines.

 Daily Calories Calculator mashup

To learn how to recreate this example, click the text in the upper-right corner of the page. Then, to find sample code and instructions, click a specific area on the page, such as the search box or meal input worksheet. 

Sample code and instructions for recreating Meal Input worksheet

In this example, select a place to visit, and you'll see information about the area's average temperature and precipitation, along with monthly visitor patterns. (Fair warning: This information is intended only for illustrative purposes, so please don't rely on it for actual trip-planning activities!) You'll also see related information about your destination appear in Bing Maps.

Destination Explorer mashup 

To find out how to we created the Destination Explorer mashup, refer to this how-to guide.

We hope you're as excited as we are about interactive data mashups, and that you'll find helpful as you create with your own. Feel free to ask questions in the Excel Web App forums or use the interactive code explorer, where you can experiment with our API right there on the site. Developers and JavaScript experts should feel right at home!

If you have comments, questions, or suggestions, please let us know by leaving a comment here or on

View the original article here

Sunday, December 25, 2011

Now where on the network did I save my workbook?

I ask myself that question a lot, especially since I typically save my workbooks to SharePoint team sites on our corporate servers. If you, too, save and share files on your network, you can make your life easier by adding the Document Location box to the Quick Access Toolbar.  

Document Location box on Quick Access Toolbar

The Document Location box shows you exactly where your file is saved. If you need your coworkers to review or edit your workbook, just paste the link from this box into an email message and send it to them. Trust me—sending a link is way better than sending a file attachment!

Here's how to add this box to the ribbon:

Click the arrow next to the Quick Access Toolbar, and then click More Commands

Arrow that opens shortcut menu

In the Excel Options dialog box, in the Choose commands from box, click Commands Not in the Ribbon.
Scroll down to the Document Location command and double-click it to add it to the Quick Access Toolbar.

Document Location command in Excel Options dialog box

Click OK. At this point, you should see the Document Location box on your Quick Access Toolbar. (If you ever want to get rid of it, right-click the border of the box, and then click Remove from Quick Access Toolbar.)

This procedure applies to Excel 2010, but it works pretty much the same way in Excel 2007. To quickly open the Excel Options dialog box in Excel 2010 or earlier, press ALT, and then press T, O.

-- Anneliese Wirth

View the original article here

When a macro won't cut it, try a VBA script

Nerdy man arm wrestling tattooed manAs an Excel MVP, I know that Excel is an extraordinarily powerful tool that can be used to simplify work in countless situations.  For example, fellow MVP Beth Melton recently designed a Gradebook template in Excel that makes teachers’ lives a whole lot easier. Based on Excel Tables technology which is built in to Excel 2010, the template includes grade calculation formulas and it even outputs a student progress report. All teachers need to do is select a student from the list, and the report is formatted and ready for printing.

Teachers who found this template on immediately liked what they saw and put it to use. Several teachers, however, wanted to make the template even more useful. They asked us whether the template could print all student progress reports at once.

To tackle this task, I knew recording a new macro wouldn’t cut it. It’s true that you could record a macro to print all of the class summaries in a batch. But this would not be a great solution because recorded macros are not smart. If the teacher were to add or delete students in the table, the macro would not function properly because the workbook would have been changed in a way that the recorded steps cannot fathom.

VBA is a fully fledged programming language built into Excel (and all other Microsoft Office applications, which supports variables (a place to store data) and programming logic.  This allows it to interact with virtually all of Excel’s built-in application objects, creating a much more powerful way than macros to automate user tasks. With it, I knew I could create a tiny program, integrated into the Gradebook template workbook, which would let teachers print summaries in a batch for all of the students no matter how many new students were added or deleted.

Logic and variables are what separate a program in VBA from a mere recorded macro. If you are familiar with the IF() worksheet function, then you know the difference. VBA has its own version of IF and several other constructs that allow for the execution of logic to branch, depending on circumstances. Add looping to the flow of execution and defined variables that can hold different values at different points in that flow, and a program can run circles around any recorded macro, even though both are “coded” in VBA.

Figure 1 clearly lays out the algorithm that is needed to implement printing the student summaries in a batch. All algorithms are directions (or a recipe) that guide your coding.  This one is simple:

Figure 1. Batch Print Algorithm.

All of the Office applications have a built-in editor for VBA. To display the editor simply press the ALT and F11 keys on the keyboard simultaneously.

Figure 2. Animation of how to insert a VBA code module.

On the left-hand side of the editor you will notice the Project Explorer, listing the worksheets in the Gradebook. While we could place VBA code in any of these worksheets, the best practice is to add a dedicated code module to the workbook. Just click on the Gradebook in the Project Explorer, and then select Module from the Insert menu at the top of the editor.

Notice that Module1 is added to the workbook. Now double-click on the module and you’ll be presented with a blank area on the right. This is where you enter the VBA code. The very first thing we should do at the very top of this code module is type the words: Option Explicit.

This is another best practice and should be at the top of all code modules. This forces the VBA interpreter to insist that all variables are defined before they are used. This may seem silly, but in larger programs this simple step can save untold grief when debugging your program.

To run our coded recipe, it needs to be contained in a defined subroutine. This is done by simply typing the keyword, Sub, followed immediately by the name we wish to assign to the routine. Let’s call ours PrintAllSummaries:

Sub PrintAllSummaries()

End Sub

Notice that as soon as we enter the line, the editor adds another line below it to terminate the subroutine container. All of the code for our little program will be entered between these two lines.

Next we need to define three variables that will be used while the program is executing:

        Dim i as Long
Dim sCount as String
Dim vStudents as Variant

Dim is the VBA keyword that defines a variable. It stands for dimension. So the first line here reads, dimension i as long. This creates a variable that we can refer to by the name of “i,” and it is dimensioned as a long integer. In VBA when you create variables, you specify what kind of data each variable is allowed to hold. A Long (integer) refers to numeric data with no decimal component. A String variable type is one that will hold text values. And a Variant is one that can hold any kind of data. So we know sCount will hold text and something interesting must be in store for vStudents.

Now that our variables are defined, the very next step is to specify what should happen if an error occurs during program execution:

    On Error Resume Next

Error handling is a large subject, but this little line of code is perhaps the easiest way to implement very basic error management. It simply tells the compiler to move on to the next line of code if any error is encountered. For many circumstances this would not be sufficient, but for our little program, this works well.

Now let’s get a list of all of the students in the class. It is possible to pluck them one at a time from the worksheet when needed, but another best practice is to retrieve all of the data at once. Fortunately this template comes with a defined name that references the column in the table that holds all of the student names: StudentLookup. It is currently being used to produce the data validation drop list in cell B8 of the Student Summary sheet.

So we can retrieve the entire list of students into our variant variable with the following line:

    vStudents = [StudentLookup]

What this line really does is place a two-dimensional array within vStudents. Arrays are variables that contain more than one value at the same time. A two-dimensional array has rows and columns, just like a worksheet. To get at the student names by referencing this array is hundreds of times faster than repeatedly referencing the worksheet cells to get the names. Not that it would make much difference on this tiny program, but again, best practices.

Now we are ready to code the algorithm. In actuality, the code to implement our algorithm is short, but we need to add some supporting code to verify that a teacher truly wishes the batch of reports to be sent to the printer. For example, a class of 35 students will result in 35 sheets of paper being printed. If the routine is run by accident, we had better give teachers a way to opt out.

To do that, we need to display a prompt that tells a teacher how many reports will be sent to the printer, and asks if they wish to continue. To do that properly, let’s temporarily set i equal to the number of elements in the array, which is exactly the number of students in the table:

    i = UBound(vStudents)

And now we can use our text variable to hold that number and the correct plurality of students:

         If i > 1 Then
sCount = i & " students "
sCount = i & " student "
End If

The ampersand character joins strings of text together. Now we can use the text string stored in sCount as part of the prompt we display to the user. In VBA, the easiest way to prompt the user is to use the MsgBox command. MsgBox can be used like a function in this manner:

    If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then

    End If

Notice the underscore characters at the ends of the lines. In VBA, these are line continuation characters. We use them so we do not need to scroll horizontally to see an entire line. So the first three lines are really one line of VBA code. vbYes is a built-in constant in VBA. If the user clicks the “Yes” button when the prompt is displayed, then MsgBox will return a value of vbYes.

Whatever we want to happen when the user clicks the “Yes” button should be placed between the IF and End If lines above. If the user does not click the “Yes” button on the prompt, then our little program will end because there will not be any more code below this block.

Now all that is left to do is place the coding of our recipe within the IF/End If block:

            For i = 1 To i
[StudentName] = vStudents(i, 1)
Sheet2.PrintOut IgnorePrintAreas:=False

That’s the whole recipe, the algorithm from Figure 1, coded in VBA. Tiny isn’t it?

Notice the For/Next block? This is one of VBA’s many looping structures. It is very simple. It causes the flow of execution to loop a predefined number of times. If it read, For i = 1 to 10, then the flow would loop ten times and on each iteration, the value stored in the variable i would increase by one, starting with the value of one.

In our case, we are reusing the variable i. Since i already has a value before this loop is encountered, the loop will iterate whatever number of times is stored in i prior to the loop. Remember that number is equal to the number of students in the class.

So what happens in this loop? Cell B8 on the Student Summary worksheet was named, “StudentName” by the template builder. We can reference that cell by placing its name in square brackets. Consequently the following line will populate that cell with the current student in the list:

                [StudentName] = vStudents(i, 1)

Remember vStudents contains our two-dimensional array of student names, organized into rows and columns. Even though it has two dimensions, all of our students are in the first column, so we can hard code the value of 1 in the second index. Notice that the variable, i, is in the first index. This is where the rows are. So when i is one, the first student is retrieved from the array; when i is two, the second student is retrieved; etc. After we place the current student name in cell B8, we need to let the workbook recalculate and update the report to show the data for the current student. This actually happens automatically, but to be safe we can use the DoEvents command, which momentarily allows the operating system to process queued events.

Finally we print the report for the current student:

                Sheet2.PrintOut IgnorePrintAreas:=False

The code name for the Student Summary worksheet is Sheet2. This can be seen in the project explorer in the Visual Basic Editor. This allows us to refer to that sheet as simply Sheet2 from VBA. One of the many methods that worksheets expose to VBA is the PrintOut method, which prints the current sheet to the default printer. We call it via a dot after the sheet name. It has many optional parameters that can be tacked on at the end. We have chosen to honor the defined print areas, as defined on the Page Layout tab of the Ribbon. That’s it!

Remember that the For/Next loop ensures that a report is printed for each student.

Now save the workbook. You should select the *.xlsm file type when you do. This is for workbooks with macros, or VBA code in them.

Here is the entire subroutine:

Sub PrintAllSummaries()
Dim i As Long
Dim sCount As String
Dim vStudents As Variant

    On Error Resume Next

    vStudents = [StudentLookup]
i = UBound(vStudents)

    If i > 1 Then
sCount = i & " students "
sCount = i & " student "
End If

    If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then

            For i = 1 To i
[StudentName] = vStudents(i, 1)
Sheet2.PrintOut IgnorePrintAreas:=False

    End If
End Sub

As you can see, this routine is custom coded to enable batch printing of the reports produced by this particular workbook. Similar techniques can be employed to create a custom batch printing solution for just about any workbook. The key is updating the correct cell or cells with the appropriate data and iterating the proper number of times. These details will be different in every situation, but the technique is the same. To run the routine, you can select it from the Macro dialog in the normal Excel window. Press ALT-F8 on the keyboard to bring it up. Alternatively, you can insert a text box on the worksheet and assign the macro to run when the text box is clicked. To do so, right-click the text box and select Assign Macro, and then select PrintAllSummaries from the list.

With Visual Basic for Applications you can automate virtually every part of Excel, and all of the other Microsoft Office applications as well. While this article has demonstrated an almost trivial (yet very useful) solution using VBA, truly impressive custom solutions can be developed this way. This author has made a living doing just that for a very long time. VBA is a subset of the venerable Visual Basic 6.0 general purpose programming language. Microsoft very cleverly incorporated this subset into the Office Suite, while at the same time exposing hundreds of native Office objects to the programming language. VBA’s heritage is much older than Excel, but the marriage of the technologies has produced an incredibly potent tool to develop custom business solutions.

To take a peek at the working script, download the Excel 2010 gradebook template.

--Daniel Ferry

Daniel Ferry is a Microsoft Excel MVP and solution developer. He operates an Excel enthusiast website and blog called Excel Hero. He also operates the Excel Hero Academy, where he teaches advanced Excel, online, to students from all over the world.

View the original article here

Saturday, December 24, 2011

Poll your friends with Photo Vote

Having trouble deciding which movie to see, which outfit to buy, or which celebrity hairstyle to copy? You're in luck, because our very own Office Labs team just released Photo Vote, a free Facebook app that lets you poll your friends on, well, anything you want! (Fair warning: people may decide you need a Bieber.)

In Facebook, create a poll with up to three photos and captions, and then post it on your wall. As your friends vote on the options, Photo Vote collects their votes, so you can see which choice is most popular. When the votes are in, you can interact with the results in an embedded spreadsheet, or take it a step further and geek out on the data in Excel. 

From start to finish, creating a poll should take about 5 minutes, tops. You can create polls in Facebook or on the go, from your Windows Phone.

First things first, you need the Photo Vote app.

 For this post, I'll walk you through the process of creating a poll in Facebook.

In Facebook, click Create a Poll, and then add a title, along with photos and captions. 

To post your poll on your Facebook wall, click Publish.

Photo Vote collects the votes for you and voila! The people have spoken:

Now, this is the Excel blog, and we're all data-heads here, so you know what's coming next...

To slice-and-dice poll results, click the Experience the Data link underneath your photos:

The results of your poll now appear in an embedded spreadsheet:

You can play around with the data by clicking the gender, relationship status, and age range filters (slicers) that appear to the left of the poll results.

Here are a couple of other things you might try:

To close the embedded spreadsheet and return to the poll view, click the Return to Poll View link underneath the black bar.

Photo Vote is brought to you by two teams here at Microsoft—our Office Business Intelligence team, and our Office Labs team, which is a group that test drives new concepts and ideas. Photo Vote is available through the end of the year, and we want to know what you think about it. If you're so inclined, please leave a comment on this post or in the Photo Vote forum.

Have fun!

-- Anneliese Wirth

View the original article here

Friday, December 23, 2011

Formula Forensics. No 007 – Sumproduct

One of the most asked questions within the posts and Forums at is “How Does Sumproduct work ?”.

Rahul recently asked for an example in Excels Sumproduct Formula post;  Comment No. 55.

So today in Formula Forensics we will take a look at just that with a few worked examples.

Excels help defines Sumproduct as:

So what are these arrays referring to:

An array in Excel can be :

A manual Array:     {10;20;30}

A Range:              A1:A3

A Named Range: MyRange1

Where MyRange1 is defined as a defined range in the Name Manager.

A Named Formula: MyRange2

Where MyRange2 is defined as a Formula returning a range in the Name Manager.

Lets look at each

You can follow along in the Example file on Sheet1

An Array

In C2 type: =SUMPRODUCT({10;20;30})

Excel will display 60, which is the Sum of the array elements =10+20+30

A Range

C7:          =Sumproduct(C4:C6)

Excel displays 60, which is the Sum of the cells from the range C4:C6 =10+20+30

A Named Range

In the Name Manager or Name Box define a Named Range

MyRange1:         =Sheet1!$C$4:$C$6

Then in C10 type:

C10: =Sumproduct(MyRange1)

Excel displays 60, which is the Sum of the range elements =10+20+30

A Named Formula

In the Name Manager define a Named Formula

MyRange2          =OFFSET(Sheet1!$C$3,1,0,3,1)

Then in C12 type:

C12:       =Sumproduct(MyRange2)

Excel displays 60, which is the Sum of the range elements from cells C4:C6 =10+20+30

You may be asking why use Sumproduct when we can use a simple Sum to add up 3 numbers?

The answer is to show you what Sumproduct is doing, it is Adding up each Array element.

Remember back at the start where we saw the Definition of Sumproduct,

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

Only Array 1 is required, Array 2, Array 3 etc are optional, that’s what the square brackets [ ] mean.

Goto Sheet 2 in the Example file:

We will look at a simple example using two arrays

The data consists of Sales data.

Often we want to know what the total sales are

We do this by  adding a Sales column

Which multiplies the Qty and Price columns

And then Sum (Add) up this new column

Returning our Total Sales of 15,000

Now we can manually check the above as the numbers are simple eg: 100*20 = 2,000 etc

And we can sum up the Sales and see that we in fact had total sales of 15,000

Well this is exactly what Sumproduct is made to do:

In a Blank cell enter: =SUMPRODUCT(D4:D8,E4:E8)

Excel will return 15,000.

So what is Sumproduct doing?

Lets look inside and see what’s going on

In the Example File, Sheet2, H1 there is a copy of the data laid out as below

Note that our formula =SUMPRODUCT(D4:D8,E4:E8)

Has two Arrays

Array 1: D4:D8

Array 2: E4:E8

Note that each corresponding Array Element is multiplied together

100 x 20

20 x 200 etc

These are the products of the two Arrays

Finally the Products are Added together and the correct answer 15,000 is returned.

So Sumproduct is the Sum of the Products of the Arrays

Of course we can extend that to a large number of Arrays, columns in this case, if we wish.

In the above two examples we saw that Sumproduct can Sum a single Array and can Sum the Product of two or more Arrays.

We can use that to our advantage and build logic into the arrays, allowing us to optionally include some array elements and leave out others.

Sumproduct will always add up the product of all Arrays.

So by including an Array where the elements within the Array that we don’t want to Sum are Zero and the Elements within the array that we do want to Sum are 1 we can control what is included in the final Summation.

Goto our Example File on Sheet3

Lets say we only want to include the Sales from our Northern Region

One way to do this is to purely delete the other entries

But what if we could do that without altering our worksheet or there are thousands of rows of data?

This is where Sumproduct comes into its own.

What we need to do is add some logic to our equation, effectively doing:

Lets try it with Sumproduct


Excel displays a -

Excel doesn’t know what to do with the True/False and so converts them to 0

We can force excel to evaluate these as numbers by adding a simple “1*”


Excel now displays 5,000 the total sales from the North

To see what has happened in F16 type: 1*{FALSE;TRUE;FALSE;FALSE;TRUE}, but don’t press Enter press F9 instead.

Excel displays ={0;1;0;0;1}

The use of the 1* has converted each of the Array elements from a True/False to a 1,0 respectively.

So our 3 arrays are now:

Now adding an Array of 1*{FALSE;TRUE;FALSE;FALSE;TRUE} every time we wanted to add some numbers isn’t a practical solution.

Excel has the ability to work construct an Array on our behalf!

In E18: enter  =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”North”))

Excel will display 5,000

So 1*(C4:C8=”North”) is exactly equal to our previous array 1*{FALSE;TRUE;FALSE;FALSE;TRUE}


At the heart of this is that Excel is evaluating each cell in the Range: C4:C8 against our required logic =”North” and setting up an Array for us internally.

The power of Sumproduct is therefore in that we can now simplify and extend

In cell E20 type: North

In cell F20 type: =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=E20))

Excel will display 5,000

This simple addition allows us to vary the Summation based on the value in E20

We don’t need to multiply our logic array by 1, we can actually use any number or another Array.

In cell F22 type: =SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=E20))

This works as (C4:C8=E20) is returning an Array of True/False which get converted to an array of 1/0’s when subject to any maths.

The Math in this case is the multiplication by the 2nd Array (E4:E8)*(C4:C8=E20)

In Cell F24 type: =SUMPRODUCT(Qty, Price *(Region=SalesRegion))

Excel will display 5,000

But notice that by using Named Ranges/Formula how simple the logic of the equation has now become.

In Comment No. 55: Rahul asked, “Can you give an example work sheet of above example”

Sheet 4 in the Example File is the answer.

In Cell C23: type: =SUMPRODUCT(- -(A2:A21=”Luke Skywalker”),- -(B2:B21=”West”),C2:C21)

Excel will display 141, which is the sum of the Sales made by Luke Skywalker in the West Region.

However using what was learned above, this is better simplified to:

C26: =SUMPRODUCT((Name=SalesMan)*(Region=SalesRegion)*Sales)

In the formula above Chandoo has used what is known as a Double Unary, which is 2 – signs next to each other (I have inserted a space above to make it more legible).

Two – signs are the same as saying

- -(A2:A21=”Luke Skywalker”) = -1 x -1 x (A2:A21=”Luke Skywalker”)

-1 x -1 is 1

Technically this is the most efficient way for Excel to perform any maths on the Array

- -(A2:A21=”Luke Skywalker”)

So that the Array of true/Falses made by (A2:A21=”Luke Skywalker”) is converted to an Array of 1/0’s for use in Sumproduct.

At the slight expense of speed but for improved readability and understandability by others I prefer the use of 1* instead of - – and you will mostly see that convention in my posts.

Chandoo:            - -(A2:A21=”Luke Skywalker”)

Hui:                       1*(A2:A21=”Luke Skywalker”)

In fact any maths performed on the array will convert its contents to an array of 1/0’s, so long as the maths doesn’t change the Arrays values

For a real good discussion on this topic have a look at the post The Venerable SUMPRODUCT at

You can download a copy of the above file and follow along, Download Here.

You can learn more about how to pull Excel Formulas apart and what makes them tick in the following post:

Formula Forensic Series:

I am running out of ideas for Formula Forensics and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.

This will be the last Formula Forensics Post for 2011, but rest assured that we will be returning in early 2012.

I’d like to take the opportunity to thank Chandoo for allowing me the space and freedom to post pretty much what ever I’ve wanted at I hope you have enjoyed my contributions to the community over the past year.

On behalf of Eva and myself I’d like to wish you all a very Merry Xmas and a Happy and Safe New Year ahead

RSS feed for comments on this post. TrackBack URI

View the original article here