Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven’t been using it for big projects, or client work.
Recently though, I’ve been testing Power Query, and the things that it can do are very exciting. I’m just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.
Once the data is combined, you can filter and sort it, all in one place. Or, create a pivot table from the combined data. It’s much easier, and with better results, than the old methods of working with Multiple Consolidation Ranges.
In this tutorial, from the Lynda.com website, you can learn 5 helpful Excel keyboard shortcuts, for tasks such as toggling the Ribbon on and off, and showing a print preview.
I use the mouse, and buttons on the QAT, for most of these commands, but if you’re a keyboard person, you might like a couple of these shortcuts. Or did you know all of them already?
Charles Williams explains how Excel handles array expressions and constants in single-cell and multi-cell Excel formulas. He’s filling in the gaps in the official documentation. Have a cup or two of coffee, before you dive into this one!
Here’s a quick Power Pivot tip from Melissa Coates (SQL Chick), so you can see the calculated measures in alphabetical order. That makes it easier to find things in a long list. You can do the same thing for the field list in a regular pivot table.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
Dashboard Course
Mynda Treacy has opened registration for her acclaimed Excel Dashboard Course, and you’ll get 20% off, if you sign up by January 22nd. I reviewed this excellent dashboard course when it first opened, and just updated my review, after taking a look at the latest version. You can read my detailed review here. The course is an even better investment now, with many new videos added.
Mynda is also offering a free introductory webinar on How to Build Excel Dashboards. Get the details on this one-hour session, and sign up for a date and time that is convenient for you.
PASS BA Marathon
Feb. 3, 2015 — Sign up now, for this free online preview of the upcoming Business Analytics Conference. There will be 6 live webinars, including “Productivity Revolution in Excel”, led by Chandoo (Purna Duggirala) and Avi Singh. Unfortunately, it has nothing to do with bacon, which was my first thought, when I saw the website name, “passbaconference.com”.
Excel Summit
Amsterdam Excel Summit, April 13-15, 2015. Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year’s summit was an outstanding success, and registration will open soon, for this year’s event. Bookmark this page, for event details and registration: Amsterdam Excel Summit 2015
Share Your Events and Articles
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
If you’ve highlighted cells with conditional formatting, what’s a quick way to delete the rows those cells are in? Someone asked that question on one of my old blog posts last week. That article showed how to use the Find command, to get a list of cells that contain a specific word. Then, delete the rows for those cells.
It’s a handy trick, but won’t work to select cells that are colored with conditional formatting.
Doug Glancy used Excel’s camera tool, and some VBA, to create a mesmerizing “selfie” of a range which has conditional formatting applied. Doug describes the results:
Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.
I downloaded the sample file, and changed the colours, to match my Contextures logo. I’m not sure if this has too many practical applications, but perhaps you can use it to hypnotize your boss, and get a big raise.
On my pivot table blog yesterday, I wrote an article about using counts in a calculated field. A helpful tip is buried in the 8-minute video that shows how to create the calculation, so I’m posting the tip separately here.
This tip can help prevent data entry problems, if you’ve set up a named Excel table for a client or co-workers to use, or even if you’re the only one using the table. Excel will automatically enter the data for you!
Happy New Year! I hope you had a relaxing end to 2014, and great start to 2015.
If you’re working in Excel at the beginning or end of a year, dates can be a problem. Usually, you can enter the day and month, then press Enter, and Excel will add the year.
Most of the time that works well, but if you’re doing December month end reports in January, Excel will add 2015 – the current year. Remember to manually enter the year to the date, if it’s not the current year – 12/31/14. And that means typing 3 extra characters! Who has time for that?
Today’s challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.
Find Specific Dates
In previous examples, we’ve seen how to check if a specific date falls within a date range. For example, if you have a list of orders, you can use SUMIF or SUMIFS, to add up all the orders between a start and end date. You can see the written instructions for this on my website.
Guest Visits
It is a little trickier to count hotel guests though, because the booking table only shows the guest arrival and departure dates. We have to use those dates, to see if any part of a guest’s visit overlapped our date range.
For this report, we want the number of guest who were in the hotel during the reporting period of December 7th to 9th. I’ve highlighted the records that should be included, when we create a formula to count the guests.
When Did the Guest Leave?
To calculate if the guest’s visit was within the date range, we’ll start with two short formulas.
The first formula will check the guest’s departure date – was it on or after the start of our date range? (If they left before the date range started, they won’t be counted)
The report start date is in cell C3, and guest’s departure date is in cell C6, so the formula is:
=C6>=$C$3
The first 5 guests all left before December 7th, so the result in those rows is FALSE.
When Did the Guest Arrive?
The second formula will check the guest’s arrival date – was it on or before the end of our date range? (If they arrived after the date range ended, they won’t be counted)
The report start date is in cell D3, and guest’s arrival date is in cell B6, so the formula is:
=B6<=$D$3
Guest #11 arrived on December 10, and that is after the report end date of December 9th, so the result in that row is FALSE.
Both Results TRUE
If the result of both formulas is TRUE, then the guest stayed at the hotel during the reporting period:
they arrived on or before December 9th
they departed on or after December 7th
We’ll use a SUMIFS formula to check those two columns, and sum then number of guests in column E. I’ve changed the column headings in F and G, to Dep Check and Arr Check.
The result is 11, and that matches the total when the numbers are selected in the manually highlighted rows. You can see that automatic calculation in the Status Bar.
Download the Sample File
To see how the formulas work, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the Functions section, and look for FN0036 – Count Hotel Guests in Date Range.
The zipped file is in xlsx format, and does not contain macros. It also has another, more complex, example of counting in a date range. Guests are counted by their hotel loyalty program level, and the number of nights booked in the date range is also calculated.
Video: Count Numbers in a Range – COUNTIFS
Using a simpler example, this video shows how to use the COUNTIFS function to count cells based on a range of numbers.
The minimum and maximum numbers are entered on the worksheet, so it’s easy to change the number range, when needed.
Thank you for reading my Excel Roundup every Monday, and I will be taking a couple of weeks off, to enjoy the holidays. There will be a blog post here this Thursday, and the next roundup will be posted on January 5th.
To help you celebrate, here is a no-macro animated Christmas tree, created in Excel, of course. Just move the scroll bar, to add lights, tinsel, a star, and presents, to the tree. You could use this technique for less festive projects too, like a business dashboard. Read the details on my Contextures website.
It’s hard enough to select one item from a long drop down list – how can we make it easier to select multiple items for a cell?
In the screen shot below, the worksheet has code that lets you select multiple item from the drop down list. Each new item is added to the cell, instead of replacing the previous selection.
Use a ListBox
To see more of the list, and select multiple items at once, you can use a ListBox, embedded in a UserForm. When you click OK, all of the selected items are entered in the cell, separated by commas.
This technique works for drop down lists that are based on a named range, such as MonthList, or AllProducts.
Really Long List
However, if your list has thousands of items, it can take a while to scroll through a ListBox too. So, to make it easier to check items in the list, I’ve created a new sample file, based on the previous ListBox file.
It has a combo box at the top of the UserForm, and you can start typing an item name there. When the correct item shows up, click Add, or press the Enter key, to add it to the list.
You can also use the ListBox, to check or uncheck items.
When you’ve finished selecting items, click OK, to add all the items to the cell.
In the sample file, there are two versions of the technique –
one opens the ListBox UserForm when you click on a cell with a drop down list
one opens the ListBox UserForm when you double-click on a cell with a drop down list
Download the Sample File
To see how this technique works, you can download the sample file from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0067 – Select Multiple Items in ComboBox or ListBox
The zipped file is in xlsm format, and contains macros. Enable the macros, when you open the file, if you want to test the code.
To see the code, right-click on the DataEntry_Click or DataEntry_DoubleClick sheet tab then click View Code.
