Excel Roundup 20140602

At PC World, Julie Sartain shows how to set up a workbook with a sheet for each month, and 3D formulas to summarize the Year to Data expenses. It’s not the way I’d set it up, but that’s a topic for another blog post.
What struck me was the comment,

“One of Excel’s greatest tricks is the dimensional or 3D reference…In Excel 2013, Microsoft has beefed it up with enhanced options and formulas.”

There are new functions, and other features in Excel 2013, but I don’t know of any new options in this area – did I miss something?
Continue reading "Excel Roundup 20140602"

Auditing Excel Formulas Quick Tips

For the past couple of weeks I’ve had lots of trouble with this blog. It’s been up and down, and I’m sorry if you’ve come to visit, and found the doors closed! The sturdy-looking door shown below is at Brock’s Monument, in Niagara-on-the-Lake, which I visited earlier this month.
So, I’ve turned off a few things on the blog, like subscribing to comments, to see if that helps. Maybe Dick Kusleika, at Daily Dose of Excel, has some tips for me, if he’s sorted out his blog problems.
Continue reading "Auditing Excel Formulas Quick Tips"

Create IFRAME Code in Excel

If you want to embed a YouTube video on your website, YouTube will create some IFRAME code for you. If you want to embed content from somewhere else, you might have to create your own IFRAME code, or find a free generator somewhere in the interwebs. Or, you can download my sample file, and create IFRAME code in Excel.
Continue reading "Create IFRAME Code in Excel"

Unique Count in Excel Pivot Table

A pivot table can sum and count and average, and do several other functions, but so far, it doesn’t have a Unique Count (Distinct Count) function. If you’ve built a Power Pivot pivot table, you’re in luck – it does have a DISTINCTCOUNT function. Here's how to get a unique count in Excel Pivot Table, with Power Pivot.

Continue reading "Unique Count in Excel Pivot Table"

Excel Roundup 20140519

This month, TechNet posted a video that demonstrates the spreadsheet management features that are available in Office 2013. This includes Inquire, and Spreadsheet Compare. Here’s a screen shot of the worksheet relationships in one of my sample Excel files, using the Inquire add-in.
worksheet relationship in Inquire add-in
The video is 30 minutes long, and you can find a list of articles on their website, if you’d rather read than watch.

Or watch on YouTube: Spreadsheet Controls in Office
Continue reading "Excel Roundup 20140519"

Excel Roundup 20140512

The Tech Republic blog takes a look at 5 free Excel add-ins, that you can download from the Microsoft Store website. It looks like they only work with Excel 2013 – have you upgraded to that version yet?

One of the add-ins is People Graph, from Microsoft, and it lets you infographic-style charts, with eye-hurting colours, and a variety of shapes. Do you agree with the author’s description of this add-in?

“Rather than displaying standard bar charts, People Graph displays icons that better convey business data that's related to people (such as the number of people who purchase a product).”


Contextures Posts

Here’s what I posted last week:

  • Save filter and print settings in Custom Views, then show the selected view's name on the worksheet
  • Make simple changes to VBA code in sample files that you copy from my website, or other sites on the web.
  • Finally, for a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

  • If you’ve ever had to make changes to the VBA code in an Excel file that someone else built, you might appreciate the code that one new developer was told not to change, because it had worked just fine for years. Yikes!

Excel Resources

Here are some upcoming events, courses and new books, related to Excel.


Business Intelligence Tools for Excel Analysts, by Michael Alexander, Jared Decker, Bernard Wehbe

384 pages, published May 5, 2014

“For the first time, Excel is an integral part of the Microsoft BI stack - capable of integrating multiple data sources, defining relationships between data sources, processing analysis services cubes, and developing interactive dashboards that can be shared on the web. With these new tools, it’s becoming important for Excel analysts to expand their knowledge to include new skills, like database management, query design, data integration, multidimensional reporting, and a host of other practices.”

exceldataanalysisfordummies01Excel Data Analysis for Dummies, by Stephen L. Nelson, E. C. Nelson

360 pages, published May 5, 2014

“If you’re like most people, you probably don’t take full advantage of Excel’s data analysis tools. This friendly guide walks you through the features of Excel to help you discover the insights in your rough data. From input, to analysis, to visualization, this book shows you how to use Excel to uncover what’s hidden within the numbers.”

What Did You Read or Write?

If you read or wrote any other interesting Excel articles recently, that you’d like to share, please add a comment below, or send me an email. Please include a brief description, and a link to the article.