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?
formula3d01
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.
doorclosedbrock
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.
But now you won’t have to wander the Internet hallways, looking for a solution, dodging scam artists at every intersection. Instead, you can download my sample file, and create your code in the safety of your own Excel file.
iframecode04
Continue reading “Create IFRAME Code in Excel”

SHEET and SHEETS Functions in Excel 2013

I’ve been experimenting with the new SHEET and SHEETS functions in Excel 2013, to see how they could be used.

  • The SHEETS function counts all the sheets in a reference
  • The SHEET function returns the sheet number for a reference

sheetsfunction02
You can see a demo of these functions in the video at the end of this post.

How Do You Use These Functions?

Have you found any practical uses for these functions? I came up with a few formulas that let you do some troubleshooting.
Continue reading “SHEET and SHEETS Functions in Excel 2013”

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 PowerPivot pivot table, you’re in luck – it does have a DISTINCTCOUNT function.
For example, see a count of the stores in each region or city, instead of the number of records for each store.
powerpivotunique2013_15b
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”

Change Pivot Table Filters With Drop Down Cell

Happy Thursday! I’ve got two news items today , and you can read the details below:

  • a new sample file on my Contextures website
  • a Microsoft Consumer Camp event in the Toronto area

Pivot Table Report Filters

There are several sample files on my website that let you change all pivot tables, based on a change to one pivot table. Kevin asked about one sample, which lets you select from a data validation drop down list. In the original file, changing that drop down updated all the pivot tables in the workbook.
Continue reading “Change Pivot Table Filters With Drop Down Cell”

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).”

peoplegraph01

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.

bitoolsforexcel01

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.

  

__________________________________

bitoolsforexcel01