A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.

More Functions in AGGREGATE

The first advantage is that AGGREGATE has 19 functions, compared to SUBTOTAL's 11 functions.

Ignore Errors with AGGREGATE

Another advantage is that AGGREGATE can ignore errors, as well as hidden rows. You can choose from a list of options, for what to ignore.

Watch the Video

Watch this short video to see the steps for setting up an AGGREGATE total, and see how it differs from SUM and SUBTOTAL

Download the Sample File

To download the sample file, please visit the Excel Sum Functions Examples page on my Contextures website.

___________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Sum a Filtered List with AGGREGATE Function Please visit the Contextures page on Facebook

In the screen shot below, you can see one of the tiny icons that you can click, to go to the top of the web page. Those are handy on the web page, but aren’t much help on the Excel sheet.

See What Objects You Copied

Sometimes these objects are easy to spot, but if they’re small, or if there are lots of them, it’s not so easy.

A quick way to see all the objects that are on a worksheet is to use the Selection Pane.

The Selection Pane opens, and you can see the full list of objects on the active sheet.

To select an object, click its name in the Selection Pane. (NOTE: If the worksheet contains ActiveX Controls, you can only select those in Design Mode.)

Select All the Objects

If you’d like to select all the objects, instead of selecting them one at a time, you can follow these steps:

Delete the Selected Objects

After you’ve selected one or more objects, you can delete them.

• On the keyboard, press the Delete key, or
• On the Ribbon’s Home tab, click Clear, then click Clear All.

And if you want to see that list of shortcuts on the Microsoft site, click here: Excel Keyboard Shortcuts.

Watch the Video

To see the steps for selecting and deleting worksheet objects, please watch this short video tutorial.

_____________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Quickly Clear Objects from Worksheet Please visit the Contextures page on Facebook

Slicers take up some room on the worksheet, but you can quickly see what filters have been applied. And, unlike Report Filters, Slicers show you what is available in the other fields, after you have applied a filter.

Slicers for Excel Tables

In Excel 2013, Slicers were enabled for named tables too, so you can filter your data with a single click. They work just like Pivot Table Slicers, and are especially handy if you’re doing a presentation. You can click one of the big Slicer buttons, instead of fumbling through the filter drop downs.

Workaround for Excel 2010 Tables

Slicers don’t work on Excel 2010 tables, but if you’re using that version, there’s good news – AlexJ has developed a workaround.

There are a couple of limitations:

• You need a unique identifier in each table row.
• Changes made manually to the field filters on the table are not reflected on the slicers (you might want to hide the table filters)

Add a Pivot Table and Slicers

From the Excel table’s data, AlexJ built a pivot table, with the ID field in the Row Labels area. Next, he added two Slicers for the pivot table, using the Size and Colour fields.

Then, copy or move those Slicers to the worksheet where the Excel Table is located.

Check for the ID

A named range – DD.Filter – is created, based on column A on the pivot table worksheet. In the Excel Table, a new column is added – xFilter – and a formula in that column checks for the row’s ID in the DD.Filter range.

The formula result is TRUE or FALSE, and only the TRUE rows will show after a Slicer is clicked.

Add Some Event Code

The final step is to add some event code to the pivot table, so it filters the table after a pivot table update. The pivot table update event is fired by the user action of changing a slicer selection.

Here is the code from the SalesPivot worksheet module.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tbl As ListObject
    Dim lCol As Long
    
    Application.ScreenUpdating = False
    
    Set tbl = Worksheets("SalesData").ListObjects("Table1")
    lCol = tbl.ListColumns("xFilter").Index
    
    With tbl
        If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
    End With
    Application.ScreenUpdating = True
    
    Set tbl = Nothing
End Sub

Download the Sample File

To download the AlexJ’s sample file, you can visit his page on the Contextures website. In the Filters section, look for

FL0002 – Filter Excel 2010 Table With Slicers

The file is designed for Excel 2010 only, and you’ll have to enable macros to test the file.

_____________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Use Slicers to Filter a Table in Excel 2010 Please visit the Contextures page on Facebook

I'm staring at a huge spreadsheet showing the distances in miles between a few hundred job sites…Our data is accurate, but the users often enter the wrong mileage data because it's easy to make a mistake when scrolling…

How can I automate this process so that I can just enter the departure site and the arrival site and retrieve the distance between the two?

The Mileage Table

To find data in a lookup table, based on the row and column headings, you can use the INDEX and MATCH functions. Here’s the mileage lookup table in my sample file, with cities in Florida.

The numbers above, and to the left of the table aren’t used – they’re just there for visual verification of the formulas.

Use INDEX and MATCH

Data validation is used to create two drop down lists for city names, and an INDEX formula in the adjacent cell returns the mileage between those cities. The MATCH function finds the row for the starting city, and the column for the destination city.

Here’s the formula that returns the mileage:

=INDEX(G3:P12,MATCH(A3,F3:F12,0),MATCH(B3,G2:P2,0))

Highlight the Mileage for Selected Cities

As an extra way to verify the results, I’ve added conditional formatting in the lookup table, to highlight the cell with the mileage for the selected cities.

Here is the conditional formatting formula:

=AND($F3=$A$3,G$2=$B$3)

Download the Sample File

To see the formulas and the conditional formatting, you can download the sample file from my website. On the Sample Files page, look for FN0026 – Get Travel Distance from Mileage Chart

The file is zipped, and in xlsx format. There are no macros in the file.

Watch the Video

To see the steps for creating the lookup formula, please watch this short video.

_____________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Get Mileage from Excel Lookup Table Please visit the Contextures page on Facebook

• Upgrade your cable package for an extra $30 per month, and that’s $360 more per year.
• Buy your lunch for $15 each workday, instead of bringing a $5 lunch from home, and you’ve added $2500 to your annual expenses.

Compare Expenses in Excel

Instead of ignoring those extra expenses, you can use Excel to calculate annual totals, and see what happens if you can cut costs.

I’ve created a workbook where you can compare two scenarios, and see the difference in annual costs.

First, enter your current spending in Scenario A, for the items that you can adjust.

Then, in Scenario B, enter the revised items – maybe you can reduce the cost, or the frequency of some items. How about golfing once a week, instead of twice? And maybe you can negotiate a lower monthly plan for your cell phone.

The worksheet calculates the annual cost for each item, and shows the difference between the scenarios.

Set the Time Units

On a separate worksheet, there is a list of time units, which is used for the data validation drop down on the Scenarios sheet. You can change the number of work weeks, and the days per work week.

Set Limits for Total Units

Some items, like golf or lawn care, are seasonal, so you can set a maximum number of occurrences for those items. For example, you play golf weekly, but only during the summer months. Instead of 52 weeks per year, the expense occurs for 25 weeks.

Calculate the Annual Quantity

To calculate the Annual Quantity, a formula checks the Max Units column, and uses that amount, if entered. Otherwise, it looks up a number from the time units table. Then, that number is multiplied by the quantity.

=IF([@[Max Units]]<>"",[@[Max Units]],
IFERROR(INDEX(TimeAnnual,MATCH([@[Time Unit]],TimeUnits,0)),0))
*[@Qty]

Download the Sample File

To download the sample file (xlsx file format), please visit the Annual Cost Calculator page on my Contextures website.

Watch the Video

To see how the annual cost calculator works, you can watch this short video.

___________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Calculate Annual Costs and Savings in Excel Please visit the Contextures page on Facebook

This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.

Testing in Excel 2010

Several people have asked about an update, so I installed the PivotPlay PLUS add-in in Excel 2010, to test it. Instead of showing up on the menu bar, the start button appears on the Excel Ribbon’s Add-Ins tab.

If the active worksheet has a pivot table, when you click the Ribbon button, you’ll see information about that pivot table. And, if the pivot table is based on an External data query, you can edit the connection information and the query string.

But, when I tried to change the connection information on a worksheet that had 2 query tables, the add-in didn’t work. The two blue tables are query tables, and the red one is an normal list. However, the add-in didn’t find any query tables.

Counting the Query Tables

The Pivot Play add-in counts the pivot tables and query tables on the active sheet. If it finds either one, the add-in opens. In Excel 2003, you could use QueryTables.Count to see if there were any tables.

Sub Count_QT_Old()
  Dim lQT As Long

  lQT = ActiveSheet.QueryTables.Count

  Debug.Print lQT

End Sub

When I used that code in Excel 2010, the count was zero, even though there were two tables based on queries.

Change the Counting Code

If we’re going to modify this add-in to work in Excel 2010, we’ll have to find a different way to check for query tables. Starting in Excel 2007, query tables changed, and now they’re part of the ListObject.

After a bit of experimentation, I found that looping through all the ListObjects, and checking their SourceType, will give a count of query tables.

Sub Count_QT_New()
  Dim lQT As Long
  Dim LO As ListObject

  For Each LO In ActiveSheet.ListObjects
    If LO.SourceType = 3 Then 'xlSrcQuery
        lQT = lQT + 1
    End If
  Next LO
  Debug.Print lQT

End Sub

When I run the revised code, it shows a count of 2 query tables, which is correct. If you know of a better way to count query tables, please let me know.

Now I’ll just have to figure out what else needs to be changed!

_______________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Counting Query Tables in Excel Please visit the Contextures page on Facebook

One way to insert cells is to select some cells, right-click on the selected range, and click Insert. Then you can select one of the Insert options.

Insert With the Fill Handle

This week, I was doing some updates on this blog, and found a tip that I posted 5 years ago. You can use the Fill Handle as a shortcut for inserting and deleting cells.

Select cells as a starting point, press Shift, and drag the fill handle to insert cells. You can use the Fill Handle to delete cells too!

Apparently I haven’t used that tip too often, because I forgot all about it! You’ll find more data entry tips on my Contextures website.

Watch the Video

Maybe I’ll remember this tip a bit longer if I make a video. To see the steps for inserting and deleting a block of cells, you can watch this short video. And watch for this tip again in 2018!

_____________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Insert Rows with Excel Fill Handle Please visit the Contextures page on Facebook

One thing that I don’t like is that, unlike previous versions, there is no Exit button or command, to close all the files. Each window has its own Close button, and if you’ve got lots of files open, it’s a pain to close each window individually.

Use the Taskbar Command

One solution is to use the Close All Windows command on the taskbar.

• Point to the Excel icon in the taskbar, and right-click on it
• In the popup menu, click Close All Windows

Add Commands to the QAT

Another option is to move the Close All and Exit commands to the Quick Access Toolbar (QAT).

• Click the Customize arrow at the end of the QAT.
• Click More Commands
• In the Choose Commands From drop down, select All Commands
• In the list of commands, select Close All
• Click Add, to move the command to the QAT
• Then, select the Exit command, and add it to the QAT
• Click Close, to return to the Excel window.

Now you can click Close All, to close all the files, but leave Excel open. Or, click Exit, to close all the files, and close Excel.

I’ve added this tip to the Excel FAQ pages on my Contextures website.

Watch the Video

Watch this short video, to see the steps for closing all the open files in Excel 2013.

________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Close All Files in Excel 2013 Please visit the Contextures page on Facebook

For example, in one of the formulas, five rows of data were omitted, so the average was incorrect. It’s easy for that kind of mistake to happen, especially if you add new data, or rearrange things, and forget to adjust your formulas.

You’ve probably read all about it, but if not, here are links to my favourite articles on this debacle:

• BBC: The Mysterious Powers of Microsoft Excel
• IEEE Spectrum: Excel Spreadsheet Error Heard Around the World
• Toronto Star: Student finds glaring spreadsheet errors

You can see a bit of the spreadsheet, and the formula with the missing countries, in this article on the Next New Deal blog:

Researchers Finally Replicated Reinhart-Rogoff, and There Are Serious Problems

No wonder the results were wrong – they didn’t include Canada!

What Can You Do to Prevent Errors?

Most of us don’t publish our Excel reports, or have them examined by PhD students. So, your mistakes might not be quite as embarrassing, but they can be costly, in both time and money.

What can you do to prevent errors in your Excel files?

• You’ll find good ideas on the European Spreadsheet Risks Interest Group (EuSpRIG) website: Best Practice
• Patrick O’Beirne, current chairman of EuSpRIG, has published a guide to finding and preventing errors: Spreadsheet Check and Control

Spreadsheet Studio

Chartered accountant, Joseph McDaid, has created an impressive free Excel add-in, Spreadsheet Studio. This add-in's tools let you review and audit your Excel files, and create comments with priority ranking and task allocation.

You can colour formulas to see if they are consistent, highlight the input and output cells, and use the Formula Explorer to go to any range reference in a formula.

To watch the two minute demo video, and download the free add-in, click here: Spreadsheet Studio

Your Suggestions

Are there other resources that you use, and ideas for preventing and finding Excel errors? Please share them in the comments.

Thanks!

__________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Catching Your Excel Errors Please visit the Contextures page on Facebook

Here’s how the tool works:

• Click a button to select a group of cells that you want to work with, like this list of months.
• Then, click the up and down arrows, to scroll the list through the selected cells.
• When you’re done, click the Exit button, to “release” the scroll area.

In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February in the second row. I clicked the Up arrow twice, and now March is at the top of the list.

Cautions Before Using

In his sample file, Jim lists a few things to consider, before you use the code in one of your own files.

1. The Custom Scroll Area program may alter...
   • Cell interior colors
   • Conditional Formatting
   • Data Validation
   • Formulas
   • Merged cells
2. Undo (on the edit menu) will not undo custom scrolling.

Possible Uses

Jim Cone sent me this file, because he thought it was a unique tool, but he couldn’t think of a practical use for it. He wondered if an Excel slot machine was possible.

I don’t have any ideas, but maybe the Blue Jays could use it to rotate their starting lineup, now that baseball season is underway.

Do you have any suggestions, brilliant or otherwise?

Download the Sample File

To see the scrolling tool, and the code that runs it, you can download Jim’s sample file from my Contextures website. On the Sample Files page, go to the “UserForms, VBA, Add-Ins” section, and look for UF0021 – Scroll Items Up and Down in List

The VBA code is protected, and the password is: s

And if you have any ideas or suggestions, please share them in the comments, or contact Jim at the address in the sample file. Thanks!

__________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog . You can read the full article here: Click to Move Excel List Items Up or Down Please visit the Contextures page on Facebook