by Charley Kyd on February 18, 2013
Because the Texas and California governors have been bickering over the Texan’s attempt to poach California employers, I got curious about the distribution of the unemployment rates in the two states.
So I used my KydWeb add-in to grab the data from the Federal Reserve Bank of St. Louis. Then I summarize the data and created this chart, technically a histogram, which I set up as Charley’s Swipe File #58.
The source data consists of 361 unemployment rates for each state, extending from December, 1982, through December, 2012.
The chart shows that the unemployment rates in Texas are clumped in the upper part of the chart. That is, over the past 30 years, Texas has tended to maintain a lower unemployment rate than California.
You could apply the logic of this chart to calculate frequency distributions for your own data. The distributions would compare the number of instances for nearly any criteria you can count: orders by product by month for two sales people, failure rates by month for two products, the number of sick days taken by age by gender in the past year, and so on.
[click to continue...]
by Charley Kyd on February 18, 2013
Excel 2007 introduced its powerful Tables feature, as illustrated here. Tables allow you to sort and filter your data easily.
However, the filter capability has at least two problems. First, you can use a maximum of only two criteria to filter any column.
Second, it takes about half a dozen steps each time you change a filter. So changing filters isn’t a quick process. It would be so much easier if you could just copy and paste one or more filters to a range of cells, or even use formulas to change filters.
This figure illustrates a way around these problems. [click to continue...]
by Charley Kyd on August 19, 2012
Last month I blogged about my Complete Excel Shortcuts Workbook. So with shortcuts in mind, here are four useful ones that work from Excel in Windows 7…
The first shortcut isn’t a keyboard shortcut; it’s a mouse shortcut.
In Windows 7, in the bottom-right corner of your monitor, you’ll see an unlabeled rectangle. As this image illustrates, when you hover your mouse pointer over that rectangle, Windows tells you that the button will display your desktop.
And sure enough, that anonymous button works as advertised.
When you eventually upgrade to Excel 2013, you’ll probably use this shortcut a lot. This is because [click to continue…]
by Charley Kyd on August 18, 2012
“Export to Excel is the 3rd most common button in BI apps…after OK and Cancel.”—Rob Collie, former member of Microsoft’s Excel team, and now CTO of PivotStream.com
Whether Rob’s running joke is true or not — and he says that it likely is true — it illustrates a continual challenge: What’s the best way for Excel users to organize and report business data in Excel?
My post, Introducing Excel’s Three Types of Spreadsheet Databases, explained three general ways to store Export-to-Excel, CSV, PivotTable, and other such data as an Excel database. And in How to Set Up a Pivot Table as a Spreadsheet Database I showed how to do it with PivotTables.
Now it’s time to take the next step: Returning the data from Excel Tables and other Excel databases into your reports and analyses. [click to continue…]
by Charley Kyd on August 11, 2012
You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables.
However, I’ve never seen a description of how to do it. That’s too bad, because Excel 2010 gave Excel users the ability to use one or more Pivot Tables as a massive and powerful spreadsheet database.
If you don’t set up your Pivot Table as a database, you typically must use the GETPIVOTDATA function to return data from it. That limits your power, because GETPIVOTDATA is a “screen-scraper” function. That is, it only can return the numbers and text you see on your screen.
On the other hand, if you do set up your Pivot Table as a database, you can use Excel’s more powerful functions with it, functions like SUMIFS, SUMPRODUCT, INDEX, MATCH, and so on. In future posts, I’ll show you how these functions can give you significantly more power to return results from your spreadsheet database.
Using Pivot Tables as a database offers many general advantages. But there’s one specific advantage that’s worth considering…
The PowerPivot Advantage
Microsoft’s introduction of PowerPivot offers a new and significant benefit to using Pivot Tables as a spreadsheet database.
[click to continue…]
by Charley Kyd on August 6, 2012
Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas:
- Simple Tables, which I’ve used since Excel 2.0.
- Excel Tables, introduced in Excel 2007.
- PivotTables with a Tabular Report Layout, introduced in Excel 2010.
Database experts likely would be offended by my calling any of these a “database.” After all, these three spreadsheet databases are easy to set up and use…not at all like a “real” database. Even so, these databases work like a real database for your Excel formulas. If your reports and analyses can get their data from one of these types of spreadsheet databases, you can improve your reporting and analyses significantly.
In this post, I’ll introduce each type of spreadsheet database. Then in future posts, I’ll go into greater detail. [click to continue…]
by Charley Kyd on July 23, 2012
Several years ago, I created my Complete Excel Shortcuts Workbook. It contains more that 250 keyboard shortcuts we can use when the workbook is active.
I created the workbook as a reference, because I didn’t know of any other source that had them all. I’m fairly certain that this workbook is complete, because thousands of people have downloaded it and I’ve added the few additional shortcuts that Excel users have sent my way.
Most Excel users use some of the shortcuts frequently, like Ctrl+C and Ctrl+V. But here are five shortcuts that aren’t as well-known, and that I use almost daily. I think you’ll find them worth remembering:
To get help for a worksheet function
Suppose you need to read the help topic for a worksheet function, say the MATCH function. When you type…
=match(
…in your formula bar, you’ll see something like this:
Press Ctrl+A or click the icon where the red arrow points to launch the [click to continue…]
by Charley Kyd on July 22, 2012
The purpose of management reports should be to help readers find and track patterns of performance…quickly and easily.
That’s the attraction of charts, of course. But should we always plot the raw data? Or should we ever transform it somehow? To see one type of transformation that’s often revealing, take a look at these two figures from Charley’s Swipe File #44:
In the left figure, the gray line takes the traditional approach: It plots total unemployment claims in the US over the past two years. [click to continue…]
by Charley Kyd on June 12, 2012
In my previous post, How to Add Advanced Filter Capabilities to Excel Tables, I explained how to use a long formula within a Table to simplify complex filtering.
The formula relies on Excel’s SEARCH worksheet function, which gives us the power to search for one string within another string. The search is not case sensitive and it can use wildcards.
Unfortunately, however, SEARCH was designed to search for only one string at a time. This limitation has been a problem for me because I often need to include more than two criteria when I’m filtering data in one column.
To see what I mean, take a look at the content of four cells from the Tags column in my Excel Table: [click to continue…]
by Charley Kyd on June 11, 2012
In Excel 2007, Microsoft added the powerful Table functionality.
One useful feature of Tables is the ability to filter any number of columns. The filter control for each column allows us to search for two criteria. However, when I use these filters I often have at least two problems with them.
First, when I explore a lot of data by using many filters in several different columns, I find myself doing a LOT more clicking than I really want to do.
Second, I occasionally need to filter on more than two criteria in a column, a feature that Tables don’t offer.
So recently, I finally got tired of all that clicking; and I got tired of the two-filter limit. So I decided to search for a better way.
And I found it. [click to continue…]
