Combine Data on Two Worksheets

Combine tables with Power Query

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

Continue reading Combine Data on Two Worksheets

Excel Roundup 20150119

Weekly Excel Roundup

In this tutorial, from the 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?

Contextures Posts

Here’s what I posted recently:

You can delete worksheet rows based on a specific Conditional Formatting color Give your Excel Pivot Table a makeover so the data is easier to read

Continue reading Excel Roundup 20150119

Delete Rows With Conditional Formatting Color

Delete rows based on Conditional Formatting color

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.

Continue reading Delete Rows With Conditional Formatting Color

Excel Roundup 20150112

Weekly Excel Roundup

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,

Continue reading Excel Roundup 20150112

Add a Counter Field to Excel Table

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!

Continue reading Add a Counter Field to Excel Table

Excel Roundup 20150105

Weekly Excel Roundup

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

Continue reading Excel Roundup 20150105

Today is Excel Date 42000

Day 42000 in Excel

This is day 42000 in the Excel calendar, so maybe we'll get the "Answer to The Ultimate Question of Life, the Universe, and Everything" – a thousand times!

Continue reading Today is Excel Date 42000

Count Activities in a Date Range

guests date range

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.

Continue reading Count Activities in a Date Range

Excel Roundup 20141215

Weekly Excel Roundup

Thank you for reading my Excel Roundup every Monday, and after this week, 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.


Continue reading Excel Roundup 20141215

Select Multiple Items From Long List in Excel

Select multiple items with combo box and listbox

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.

Continue reading Select Multiple Items From Long List in Excel