Football Fun in Excel

This Sunday is Super Bowl XLIX – the last one that will start with "XL".  Next year, it will be Super Bowl L – that doesn't exactly roll off the tongue!

In 2011, I showed how you can use the ROMAN function in Excel, to change a number into a Roman numeral. That year, they were playing for the 45th time, which was XLV.

=ROMAN(A2)

Other Football Functions

There are other Excel functions that sound like they could be used in a football game. Here are a few, and I'm sure you can think of others:

CONVERT RECEIVE YIELD

Continue reading Football Fun in Excel

Excel Roundup 20150126

Weekly Excel Roundup http://blog.contextures.com/

Here is a short video, by Dennis Taylor from Lynda.com, that shows some keyboard shortcuts for formatting on a worksheet. For example:

Ctrl + Shift + - (hyphen) to remove borders in selected cells Ctrl +5 to change text to Strikethrough

He also shows how to use the ISFORMULA function, new in Excel 2013, with conditional formatting, to dynamically highlight cells that contain formulas. You can get written instructions for that technique on my website.

Continue reading Excel Roundup 20150126

Combine Data on Two Worksheets

Combine tables with Power Query http://blog.contextures.com/

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 http://blog.contextures.com/

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?

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 http://blog.contextures.com/

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 http://blog.contextures.com/

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 http://blog.contextures.com/

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 http://blog.contextures.com/archives/2014/12/27/today-is-excel-date-42000/

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 http://blog.contextures.com/

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