Today, we’ll see a little magic from the COUNTIF function – it can do the work of seven formulas in one! We’ll use it to check winning numbers in an imaginary lottery.
Continue reading “Check Winning Numbers with COUNTIF Function”
Dependent Drop Down List From a Row
There is a sample file on my Contextures site, which creates dependent drop down lists from items that are sorted in columns. You can see the lists in the screen shot below. On the data entry sheet, you select a region from the first drop down, and only the customers in that region appear in the second drop down.
Change Functions with AGGREGATE in Excel
A couple of years ago, we looked at the Excel SUBTOTAL function, and saw how you could allow users to select the function they want. In the example shown below, the total formulas are controlled by the drop down list at the top of the sheet.
Today, we’ll use a similar technique to change functions with AGGREGATE – one of Excel’s newer functions.
Prevent Scrolling on Excel Worksheet
Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling on Excel worksheet, change the ScrollArea property for the worksheet, either manually or with a macro.
Switch Languages in Excel Workbook
Our two official languages in Canada are English and French, and I’m currently working on an Excel project in which you can choose either language. The steps below show how to set things up, so you can switch languages in Excel.
Conditional Drop Down Lists in Excel
It’s fairly easy to make one drop down list show items based on what was selected in another drop down. But what about making a third drop down list? And how can you work with spaces, or other characters that can’t be used in Excel names?
How to Set Up an Excel Advanced Filter
Poor Advanced Filter! It’s hidden in a dark corner of the Ribbon’s Data tab, overshadowed by its better known, and more popular sibling, AutoFilter.
Sometimes, though, it’s worth the extra effort to use an Advanced Filter. Keep reading, to see the benefits, and learn how to set up an Excel Advanced Filter.
Create Pivot Table or Excel Table from Multiple Files
A few years ago, Excel MVP Kirill Lapin shared his code to create a pivot table from identically structured tables in two or more Excel files. His technique used a Union query in Microsoft Query, and you can see the details here.
Continue reading “Create Pivot Table or Excel Table from Multiple Files”
Click a Cell to Filter Excel Column
Here is a fancy filter trick, if you frequently have to filter your Excel tables. Click on a cell in this Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.
Dashboard Tools Add-In Giveaway Winners
Last week, Mike Alexander, from Bacon Bits Blog, provided 2 copies of his time-saving new Dashboard Tools add-in for our Excel giveaway. Here are the dashboard tools add-in giveaway winners.
NOTE: The Bacon Bits Blog is no longer online.