Share Access Data with Excel in Office 2010

Being able to share Access data with other applications is easier in Office 2010. Today we take a look at how easy it is to share data from an Access 2010 Database to to an Excel spreadsheet and more.
Share Access Data
First we’ll take a look at sharing an Access table to an Excel spreadsheet. Open the Access table you want to export and click on the External Data tab. From here you can see a bunch of different options you have for exporting the data. In this example we’re using exporting to Excel so click on that.

A window comes up asking where to save the file, the file format, and some other exporting options.

In the next screen you might want to save the Export Steps to make it easier the next time or if you’re working with several documents and need to do the same thing. From here you can also set up an Outlook task if you regularly need to repeat it.

The data goes into Excel and you can start working on it here, or send it where it needs to go.

There are a lot of other applications Access 2010 will let you export data, like text files, Word, a SharePoint List and more. There is an easy Word Merge feature so if you have a database of addresses you can easily incorporate it with Mail Merge.

Sometimes getting the data you need out of an Access Database can be a bit daunting. It looks like these tasks will be a lot easier in Office 2010, which will be great for increasing productivity.

Read more »

Working with PivotTables in Excel

PivotTables are one of the most powerful features of Microsoft Excel.  They allow large amounts of data to be analyzed and summarized in just a few mouse clicks. In this article, we explore PivotTables, understand what they are, and learn how to create and customize them.
Note:  This article is written using Excel 2010 (Beta).  The concept of a PivotTable has changed little over the years, but the method of creating one has changed in nearly every iteration of Excel.  If you are using a version of Excel that is not 2010, expect different screens from the ones you see in this article.
A Little History
In the early days of spreadsheet programs, Lotus 1-2-3 ruled the roost.  Its dominance was so complete that people thought it was a waste of time for Microsoft to bother developing their own spreadsheet software (Excel) to compete with Lotus.  Flash-forward to 2010, and Excel’s dominance of the spreadsheet market is greater than Lotus’s ever was, while the number of users still running Lotus 1-2-3 is approaching zero.  How did this happen?  What caused such a dramatic reversal of fortunes?
Industry analysts put it down to two factors:  Firstly, Lotus decided that this fancy new GUI platform called “Windows” was a passing fad that would never take off.  They declined to create a Windows version of Lotus 1-2-3 (for a few years, anyway), predicting that their DOS version of the software was all anyone would ever need.  Microsoft, naturally, developed Excel exclusively for Windows.  Secondly, Microsoft developed a feature for Excel that Lotus didn’t provide in 1-2-3, namely PivotTables.  The PivotTables feature, exclusive to Excel, was deemed so staggeringly useful that people were willing to learn an entire new software package (Excel) rather than stick with a program (1-2-3) that didn’t have it.  This one feature, along with the misjudgment of the success of Windows, was the death-knell for Lotus 1-2-3, and the beginning of the success of Microsoft Excel.
Understanding PivotTables
So what is a PivotTable, exactly?
Put simply, a PivotTable is a summary of some data, created to allow easy analysis of said data.  But unlike a manually created summary, Excel PivotTables are interactive.  Once you have created one, you can easily change it if it doesn’t offer the exact insights into your data that you were hoping for.  In a couple of clicks the summary can be “pivoted” – rotated in such a way that the column headings become row headings, and vice versa.  There’s a lot more that can be done, too.  Rather than try to describe all the features of PivotTables, we’ll simply demonstrate them…
The data that you analyze using a PivotTable can’t be just any data – it has to be raw data, previously unprocessed (unsummarized) – typically a list of some sort.  An example of this might be the list of sales transactions in a company for the past six months.
Examine the data shown below:

Notice that this is not raw data.  In fact, it is already a summary of some sort.  In cell B3 we can see $30,000, which apparently is the total of James Cook’s sales for the month of January.  So where is the raw data?  How did we arrive at the figure of $30,000?  Where is the original list of sales transactions that this figure was generated from?  It’s clear that somewhere, someone must have gone to the trouble of collating all of the sales transactions for the past six months into the summary we see above.  How long do you suppose this took?  An hour?  Ten?
Most probably, yes.  You see, the spreadsheet above is actually not a PivotTable.  It was created manually from raw data stored elsewhere, and it did indeed take a couple of hours to compile.  However, it’s exactly the sort of summary that could be created using PivotTables, in which case it would have taken just a few seconds.  Let’s find out how…
If we were to track down the original list of sales transactions, it might look something like this:

You may be surprised to learn that, using the PivotTable feature of Excel, we can create a monthly sales summary similar to the one above in a few seconds, with only a few mouse clicks.  We can do this – and a lot more too!
How to Create a PivotTable
First, ensure that you have some raw data in a worksheet in Excel.  A list of financial transactions is typical, but it can be a list of just about anything:  Employee contact details, your CD collection, or fuel consumption figures for your company’s fleet of cars.
So we start Excel…

…and we load such a list…

Once we have the list open in Excel, we’re ready to start creating the PivotTable.
Click on any one single cell within the list:

Then, from the Insert tab, click the PivotTable icon:

The Create PivotTable box appears, asking you two questions:  What data should your new PivotTable be based on, and where should it be created?  Because we already clicked on a cell within the list (in the step above), the entire list surrounding that cell is already selected for us ($A$1:$G$88 on the Payments sheet, in this example).  Note that we could select a list in any other region of any other worksheet, or even some external data source, such as an Access database table, or even a MS-SQL Server database table.  We also need to select whether we want our new PivotTable to be created on a new worksheet, or on an existing one.  In this example we will select a new one:

The new worksheet is created for us, and a blank PivotTable is created on that worksheet:

Another box also appears:  The PivotTable Field List.  This field list will be shown whenever we click on any cell within the PivotTable (above):

The list of fields in the top part of the box is actually the collection of column headings from the original raw data worksheet.  The four blank boxes in the lower part of the screen allow us to choose the way we would like our PivotTable to summarize the raw data.  So far, there is nothing in those boxes, so the PivotTable is blank.  All we need to do is drag fields down from the list above and drop them in the lower boxes.  A PivotTable is then automatically created to match our instructions.  If we get it wrong, we only need to drag the fields back to where they came from and/or drag new fields down to replace them.
The Values box is arguably the most important of the four.  The field that is dragged into this box represents the data that needs to be summarized in some way (by summing, averaging, finding the maximum, minimum, etc).  It is almost always numerical data.  A perfect candidate for this box in our sample data is the “Amount” field/column.  Let’s drag that field into the Values box:

Notice that (a) the “Amount” field in the list of fields is now ticked, and “Sum of Amount” has been added to the Values box, indicating that the amount column has been summed.
If we examine the PivotTable itself, we indeed find the sum of all the “Amount” values from the raw data worksheet:

We’ve created our first PivotTable!  Handy, but not particularly impressive.  It’s likely that we need a little more insight into our data than that.
Referring to our sample data, we need to identify one or more column headings that we could conceivably use to split this total.  For example, we may decide that we would like to see a summary of our data where we have a row heading for each of the different salespersons in our company, and a total for each.  To achieve this, all we need to do is to drag the “Salesperson” field into the Row Labels box:

Now, finally, things start to get interesting!  Our PivotTable starts to take shape….

With a couple of clicks we have created a table that would have taken a long time to do manually.
So what else can we do?  Well, in one sense our PivotTable is complete.  We’ve created a useful summary of our source data.  The important stuff is already learned!  For the rest of the article, we will examine some ways that more complex PivotTables can be created, and ways that those PivotTables can be customized.
First, we can create a two-dimensional table.  Let’s do that by using “Payment Method” as a column heading.  Simply drag the “Payment Method” heading to the Column Labels box:

Which looks like this:

Starting to get very cool!
Let’s make it a three-dimensional table.  What could such a table possibly look like?  Well, let’s see…
Drag the “Package” column/heading to the Report Filter box:

Notice where it ends up….

This allows us to filter our report based on which “holiday package” was being purchased.  For example, we can see the breakdown of salesperson vs payment method for all packages, or, with a couple of clicks, change it to show the same breakdown for the “Sunseekers” package:

And so, if you think about it the right way, our PivotTable is now three-dimensional.  Let’s keep customizing…
If it turns out, say, that we only want to see cheque and credit card transactions (i.e. no cash transactions), then we can deselect the “Cash” item from the column headings.  Click the drop-down arrow next to Column Labels, and untick “Cash”:

Let’s see what that looks like…As you can see, “Cash” is gone.

Formatting
This is obviously a very powerful system, but so far the results look very plain and boring.  For a start, the numbers that we’re summing do not look like dollar amounts – just plain old numbers.  Let’s rectify that.
A temptation might be to do what we’re used to doing in such circumstances and simply select the whole table (or the whole worksheet) and use the standard number formatting buttons on the toolbar to complete the formatting.  The problem with that approach is that if you ever change the structure of the PivotTable in the future (which is 99% likely), then those number formats will be lost.  We need a way that will make them (semi-)permanent.
First, we locate the “Sum of Amount” entry in the Values box, and click on it.  A menu appears.  We select Value Field Settings… from the menu:

The Value Field Settings box appears.

Click the Number Format button, and the standard Format Cells box appears:

From the Category list, select (say) Accounting, and drop the number of decimal places to 0.  Click OK a few times to get back to the PivotTable…

As you can see, the numbers have been correctly formatted as dollar amounts.
While we’re on the subject of formatting, let’s format the entire PivotTable.  There are a few ways to do this.  Let’s use a simple one…
Click the PivotTable Tools/Design tab:

Then drop down the arrow in the bottom-right of the PivotTable Styles list to see a vast collection of built-in styles:

Choose any one that appeals, and look at the result in your PivotTable:

Other Options
We can work with dates as well.  Now usually, there are many, many dates in a transaction list such as the one we started with.  But Excel provides the option to group data items together by day, week, month, year, etc.  Let’s see how this is done.
First, let’s remove the “Payment Method” column from the Column Labels box (simply drag it back up to the field list), and replace it with the “Date Booked” column:

As you can see, this makes our PivotTable instantly useless, giving us one column for each date that a transaction occurred on – a very wide table!

To fix this, right-click on any date and select Group… from the context-menu:

The grouping box appears.  We select Months and click OK:

Voila!  A much more useful table:

(Incidentally, this table is virtually identical to the one shown at the beginning of this article – the original sales summary that was created manually.)
Another cool thing to be aware of is that you can have more than one set of row headings (or column headings):

…which looks like this….

You can do a similar thing with column headings (or even report filters).
Keeping things simple again, let’s see how to plot averaged values, rather than summed values.
First, click on “Sum of Amount”, and select Value Field Settings… from the context-menu that appears:

In the Summarize value field by list in the Value Field Settings box, select Average:

While we’re here, let’s change the Custom Name, from “Average of Amount” to something a little more concise.  Type in something like “Avg”:

Click OK, and see what it looks like.  Notice that all the values change from summed totals to averages, and the table title (top-left cell) has changed to “Avg”:

If we like, we can even have sums, averages and counts (counts = how many sales there were) all on the same PivotTable!
Here are the steps to get something like that in place (starting from a blank PivotTable):

1. Drag “Salesperson” into the Column Labels
2. Drag “Amount” field down into the Values box three times
3. For the first “Amount” field, change its custom name to “Total” and it’s number format to Accounting (0 decimal places)
4. For the second “Amount” field, change its custom name to “Average”, its function to Average and it’s number format to Accounting (0 decimal places)
5. For the third “Amount” field, change its name to “Count” and its function to Count
6. Drag the automatically created field from Column Labels to Row Labels

Here’s what we end up with:

Total, average and count on the same PivotTable!
Conclusion
There are many, many more features and options for PivotTables created by Microsoft Excel – far too many to list in an article like this.  To fully cover the potential of PivotTables, a small book (or a large website) would be required.  Brave and/or geeky readers can explore PivotTables further quite easily:  Simply right-click on just about everything, and see what options become available to you.  There are also the two ribbon-tabs: PivotTable Tools/Options and Design.  It doesn’t matter if you make a mistake – it’s easy to delete the PivotTable and start again – a possibility old DOS users of Lotus 1-2-3 never had.

Read more »

VLOOKUP in Excel, part 2: Using VLOOKUP without a database

In a recent article, we introduced the Excel function called VLOOKUP and explained how it could be used to retrieve information from a database into a cell in a local worksheet.  In that article we mentioned that there were two uses for VLOOKUP, and only one of them dealt with querying databases.  In this article, the second and final in the VLOOKUP series, we examine this other, lesser known use for the VLOOKUP function.
If you haven’t already done so, please read the first VLOOKUP article – this article will assume that many of the concepts explained in that article are already known to the reader.
When working with databases, VLOOKUP is passed a “unique identifier” that serves to identify which data record we wish to find in the database (e.g. a product code or customer ID).  This unique identifier must exist in the database, otherwise VLOOKUP returns us an error.  In this article, we will examine a way of using VLOOKUP where the identifier doesn’t need to exist in the database at all.  It’s almost as if VLOOKUP can adopt a “near enough is good enough” approach to returning the data we’re looking for.  In certain circumstances, this is exactly what we need.
We will illustrate this article with a real-world example – that of calculating the commissions that are generated on a set of sales figures.  We will start with a very simple scenario, and then progressively make it more complex, until the only rational solution to the problem is to use VLOOKUP.  The initial scenario in our fictitious company works like this:  If a salesperson creates more than $30,000 worth of sales in a given year, the commission they earn on those sales is 30%.  Otherwise their commission is only 20%.  So far this is a pretty simple worksheet:

To use this worksheet, the salesperson enters their sales figures in cell B1, and the formula in cell B2 calculates the correct commission rate they are entitled to receive, which is used in cell B3 to calculate the total commission that the salesperson is owed (which is a simple multiplication of B1 and B2).
The cell B2 contains the only interesting part of this worksheet – the formula for deciding which commission rate to use: the one below the threshold of $30,000, or the one above the threshold.  This formula makes use of the Excel function called IF.  For those readers that are not familiar with IF, it works like this:

IF(condition,value if true,value if false)

Where the condition is an expression that evaluates to either true or false.  In the example above, the condition is the expression B1<B5, which can be read as “Is B1 less than B5?”, or, put another way, “Are the total sales less than the threshold”.  If the answer to this question is “yes” (true), then we use the value if true parameter of the function, namely B6 in this case – the commission rate if the sales total was below the threshold.  If the answer to the question is “no” (false), then we use the value if false parameter of the function, namely B7 in this case – the commission rate if the sales total was above the threshold.
As you can see, using a sales total of $20,000 gives us a commission rate of 20% in cell B2.  If we enter a value of $40,000, we get a different commission rate:

So our spreadsheet is working.
Let’s make it more complex.  Let’s introduce a second threshold:  If the salesperson earns more than $40,000, then their commission rate increases to 40%:

Easy enough to understand in the real world, but in cell B2 our formula is getting more complex.  If you look closely at the formula, you’ll see that the third parameter of the original IF function (the value if false) is now an entire IF function in its own right.  This is called a nested function (a function within a function).  It’s perfectly valid in Excel (it even works!), but it’s harder to read and understand.
We’re not going to go into the nuts and bolts of how and why this works, nor will we examine the nuances of nested functions.  This is a tutorial on VLOOKUP, not on Excel in general.
Anyway, it gets worse!  What about when we decide that if they earn more than $50,000 then they’re entitled to 50% commission, and if they earn more than $60,000 then they’re entitled to 60% commission?

Now the formula in cell B2, while correct, has become virtually unreadable.  No-one should have to write formulae where the functions are nested four levels deep!  Surely there must be a simpler way?
There certainly is.  VLOOKUP to the rescue!
Let’s redesign the worksheet a bit.  We’ll keep all the same figures, but organize it in a new way, a more tabular way:

Take a moment and verify for yourself that the new Rate Table works exactly the same as the series of thresholds above.
Conceptually, what we’re about to do is use VLOOKUP to look up the salesperson’s sales total (from B1) in the rate table and return to us the corresponding commission rate.  Note that the salesperson may have indeed created sales that are not one of the five values in the rate table ($0, $30,000, $40,000, $50,000 or $60,000).  They may have created sales of $34,988.  It’s important to note that $34,988 does not appear in the rate table.  Let’s see if VLOOKUP can solve our problem anyway…
We select cell B2 (the location we want to put our formula), and then insert the VLOOKUP function from the Formulas tab:

The Function Arguments box for VLOOKUP appears.  We fill in the arguments (parameters) one by one, starting with the Lookup_value, which is, in this case, the sales total from cell B1.  We place the cursor in the Lookup_value field and then click once on cell B1:

Next we need to specify to VLOOKUP what table to lookup this data in.  In this example, it’s the rate table, of course.  We place the cursor in the Table_array field, and then highlight the entire rate table – excluding the headings:

Next we must specify which column in the table contains the information we want our formula to return to us.  In this case we want the commission rate, which is found in the second column in the table, so we therefore enter a 2 into the Col_index_num field:

Finally we enter a value in the Range_lookup field.
Important:  It is the use of this field that differentiates the two ways of using VLOOKUP.  To use VLOOKUP with a database, this final parameter, Range_lookup, must always be set to FALSE, but with this other use of VLOOKUP, we must either leave it blank or enter a value of TRUE.  When using VLOOKUP, it is vital that you make the correct choice for this final parameter.
To be explicit, we will enter a value of true in the Range_lookup field.  It would also be fine to leave it blank, as this is the default value:

We have completed all the parameters.  We now click the OK button, and Excel builds our VLOOKUP formula for us:

If we experiment with a few different sales total amounts, we can satisfy ourselves that the formula is working.
Conclusion
In the “database” version of VLOOKUP, where the Range_lookup parameter is FALSE, the value passed in the first parameter (Lookup_value) must be present in the database.  In other words, we’re looking for an exact match.
But in this other use of VLOOKUP, we are not necessarily looking for an exact match.  In this case, “near enough is good enough”.  But what do we mean by “near enough”?  Let’s use an example:  When searching for a commission rate on a sales total of $34,988, our VLOOKUP formula will return us a value of 30%, which is the correct answer.  Why did it choose the row in the table containing 30% ?  What, in fact, does “near enough” mean in this case?  Let’s be precise:

When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.

It’s also important to note that for this system to work, the table must be sorted in ascending order on column 1!

Read more »