Excel’s Data Explorer, Pivot Chart & Grouping of Dates

/in /by

NOTE: Data Explorer is now called Power Query

We want a simple bar chart showing U.S. Governors, by party, and the year their terms end.

To do this, we use
1. Excel’s Data Explorer Preview
(a free Microsoft AddIn for Excel 2010, 2013. Sorry, Mac users. This is another feature that’s only on the PC side. Download it here:http://www.microsoft.com/en-us/download/details.aspx?id=36803 )
2. Pivot Chart
3. Group details in the Pivot Table

Please subscribe, leave comments and check out my blog at http://www.DataScopic.net

converting columnar data to sortable rows

/in /by

This is a common problem:
“I received data that’s in a long column but it needs to be in rows that can be sorted.”

To get the data arranged properly, we look for patterns. One pattern is that there’s a space before every new entry. From there, it’s easy. The video shows how to use IF statements the the OFFSET function to get the data the way we need it.

Please comment or ask questions if you need clarification or have a request for other data/Excel challenges.

Excel Uncensored: Building an App From Scratch

/in /by

Course is being given 18MAY13 in Chicago
For details & registration: http://bit.ly/11N66qW

Learn by doing! If you want to learn about wood, woodworking tools, different types of nails, etc., why not build a fancy cabinet? That’s the idea for Excel Uncensored

What you will get from Excel Uncensored:

– If you haven’t done any VBA or macros, you will get a small taste.
– Learn to troubleshoot.
– Formula triggers: keep formulas dormant until there is complete information to calculate.
– Learn to plan your spreadsheet. Some things require thinking 3 steps ahead. Don’t get to step 7 and realize that you must first un-do steps 6, 5 and 4.
– COMFORT with Excel.
– Deeper insight into what Excel can do.
– Integrate your skills into something functional.

Everything you learn in these 5 hours will help you go forward being able to develop solid, organized spreadsheets. And you might suddenly be your community’s Excel guru

Sriracha Taste Test 2: Sky Valley, Saigon Sisters & Lee Kum Kee

/in /by

Tasting Comparison of 3 sriracha brands: Lee Kum Kee, Saigon Sisters, and Sky Valley.

Yesterday we did Trader Joe’s, Lee Kum Kee and Saigon Sisters. Trader Joe’s as salty and not so spicy. Today, someone at Whole Foods was kind enough to give me a bottle of Sky Valley. Here is the tasting …

Review of 3 Sriracha brands: Lee Kum Kee, Saigon Sisters, Trader Joe’s

/in /by

It had to be done! Review of 3 brands of sriracha. Lee Kum Kee is my favorite. It could be spicier but as a complete experience, it’s the clear winner.Please comment. Share your thoughts.

Drawing or inking in Excel

/in /by

Ever wanted to draw in Excel? Highlight certain things?
The Inking feature allows you to do it.

Graphing 2 Pieces of Data on 1 Chart (Excel Secondary Axis)

/in /by

(This grew out of a blogpost on monitoring the fundraising of a nonprofit. You can see it here: http://bit.ly/100nsKY )

Sometimes we want to see 2 very different bits of data in one graph. In this case, it helps to see the donation totals and the number of donations together.

In Excel lingo, we create a Secondary Axis.

What we get from this is evidence that the campaign is coasting to an end. Even though we saw a big jump in the donation dollars in July, the number of donation continues to trend downward. This tells us that 1 or 2 huge donors showed up in July.

We could see this by just looking at the numbers but it’s much easier to grasp with a visual.

VLOOKUP: Correcting a Myth (Binary Searches)

/in /by

Many of us have given out bad information about VLOOKUPS. We’ve said that VLOOKUP always starts its search at the top of our lookup array and it goes step-by-step until it finds the closest or exact match.

That’s called a Liner Search and that’s what happens when we want an exact match. We designate that by using ‘false’ in the formula.

When we want an approximate match, we use ‘true’ and the VLOOKUP does a BINARY SEARCH. In that, the search starts in the middle of the list, eliminates half, then hops to the middle of the remaining half … eliminating chunks of the list until there is either:

1. and exact match
2. 1 choice left, or
3. an error

I detail this in the video.

—
For my annotation I use
1. Microsoft’s Snipper Tool
2. Bamboo’s pen & tablet

(Excel 2013: Slicers w/Tables) Slicers to determine trip based on time and budget

/in /by

A travel agent presented this challenge because she was always struggling when a client wanted to go on a trip to a specific destination but when she’d look it up, the client either didn’t have enough money or didn’t have enough time.

She asks:
“We always know the time needed and the budget for each destination. Can I use Excel to look at the budget and time available first, then pick from the remaining destinations.”

YES!

Here is an Excel 2013 solution using Slicers with Tables.