Excel Roundup 20150330

This week, see how to highlight items in a dashboard, change the background colour in a chart, use Excel for project management, and many more tips.

If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!

Continue reading Excel Roundup 20150330

How to Count in Excel

Learn 7 ways to count in Excel, with function tips and tricks http://blog.contextures.com/archives/2015/03/26/how-to-count-in-excel/

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:

COUNT COUNTA COUNTBLANK

For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:

=COUNTBLANK(A1:A5)

More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:

COUNTIF COUNTIFS SUBTOTAL AGGREGATE

For example, to count only the visible numbers, after filtering and/or

Continue reading How to Count in Excel

Excel Roundup 20150323

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

Have you used PowerMap in Excel? Microsoft's Excel team used it to illustrate the White House budget in 3D. You can read the details, and download the Excel file from their blog.

Continue reading Excel Roundup 20150323

Find and Fix Pivot Table Source Data

alt text: Find and fix pivot table data source problems http://www.contextures.com/excelpivottablesourcedata.html

After you create a pivot table, you might add or change records in the source data. Sometimes the pivot table does not update correctly, to show the new data. Or if you've inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.

To help with these problems, I've added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.

Continue reading Find and Fix Pivot Table Source Data

Excel Roundup 20150316

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

In this short video, Sara Silverstein shows how to analyze several years of NCAA revenue data, by using a pivot table. Can you predict which sport brings in the biggest revenues?

Or watch on YouTube: Use Excel pivot tables to analyze massive data sets

Contextures Posts

Here’s what I posted recently:

Highlight numbers that are found in another list, with conditional formatting. This example uses lottery numbers, and you could use the same technique for other types of lists, such as names, or dates. For a humorous peek at what other people are saying about spreadsheets, read the latest

Continue reading Excel Roundup 20150316

Highlight Winning Lottery Numbers

Highlight Winning Lottery Numbers http://www.contextures.com/xlCondFormat03.html#Lottery

No, I've never won the lottery, but that's probably because I don't buy tickets! Your odds of winning improve (slightly) if you actually have a ticket for the draw.

However, there are many workplaces where someone has organized a weekly lottery pool, and they have a batch of ticket numbers to check.

Instead of checking those numbers manually (and missing one or two!), you can use Excel to check them for you. It won't even ask for a percentage, if you are lucky enough to win a prize.

Continue reading Highlight Winning Lottery Numbers

Excel Roundup 20150309

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

Last week, we saw a preview of Tableau's Elastic app for spreadsheets. This week, you can see another beta add-in – BigPicture, from Palisade. It looks like Visio combined with Excel's built-in drawing tools and SmartArt graphics.

Currently, you can download a free copy of the beta, to try it out (Windows only, Excel 2007 or later).

Watch this short video, to see how it works -- the demo starts at the 1:30 mark.

Continue reading Excel Roundup 20150309

Ignore Outliers with Excel TRIMMEAN

Excel TRIMMEAN function http://www.contextures.com/excelaveragefunctions.html

From what I've seen in workbooks over the years, SUM is the most frequently used Excel function, and AVERAGE is the runner-up. Would you agree, or do you see other functions used more often than those two?

Last week, a client asked about excluding some of the highest and lowest numbers from a data set, to give a better average. It looked like some of those outliers had been incorrectly entered (an extra zero, or a mistyped numbers), and they were throwing off the results.

So, we did some tests with the TRIMMEAN function, to compare the results with a

Continue reading Ignore Outliers with Excel TRIMMEAN

Excel Roundup 20150302

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

Spreadsheets are already fun, but Tableau wants to make them even more exciting, with its newest smartphone app, Elastic. In this video, Tableau's VP of Mobile & Strategic Growth, Dave Story, opens a spreadsheet file (csv format) from his inbox, and shows how Elastic works.

I don't see any information about the expected release date or pricing.

Continue reading Excel Roundup 20150302

Resize Excel Comments With Macro

Resize Excel Comments With Macro http://blog.contextures.com/

Occasionally, for reasons unknown to anyone other than the centuries-old trolls who live in underground tunnels at Microsoft, Excel comments resize themselves. They get really wide, or narrow, and become almost impossible to read.

Continue reading Resize Excel Comments With Macro