Excel tips, trickery, and shortcuts

These are (mostly brief and mostly video) explanations of handy Excel short-cuts, time-savers, and how-to's that I have found useful over the years.

Retrieving text values with a PivotTable or with Excel cube functions.

On a number of occasions I have needed to retrieve text values (as shown in the animation below) using a PivotTable or Excel cube functions and I've seen on several Excel-related internet forums requests from others to do the same.

Text retrieved in a PivotTable

I found a solution using the PowerPivot for Excel add-in, a free download for Excel 2010 or included in certain versions of Excel 2013 and 2016. Below are links to two .xlsx workbooks (identical except one is for Excel 2010 and the other is for Excel 2013/2016) that explain how it's done. Or you can watch this video.

Thanks and credit go to a post on a Mr Excel PowerPivot forum with a response provided by Rob Collie of PowerPivotPro, an excellent PowerPivot resource.

These downloads require no registration:
Example workbook for Excel 2010/PowerPivot 2012
Example workbook for Excel 2013/2016 with Power Pivot
The 2016 workbook will also work with any version of Excel 2013 and 2016 that supports the Data Model, such as Office 365 Home Premium. However without the Power Pivot add-in you won't be able to create the necessary measures for you own applications.

Using Slicers in an Excel Cube Formula Report

Slicers were introduced in Excel 2010 to provide a better user experience in selecting slices of data from a PivotTable. But they are also ideal with Excel cube formula reports (especially when used in a dashboard application). Cube formula reports are supported with data sourced from PowerPivot models and Analysis Services cubes. This video explains how.

Two ways to create a PivotTable from an Excel range using Excel 2010 or higher

In Excel 2010/2013/2016 you can create a PivotTable from an Excel range the same as always (like Excel 2007 and similar to earlier versions). But in Excel 2010 with the PowerPivot add-in installed, or Excel 2013/2016 you have another choice. You can set the range to be a Linked Table and then create your PivotTable from the Power Pivot Data Model with a couple of extra mouse clicks.

But why would you use Power Pivot to build your PivotTable? Because the OLAP Tools functions on the PivotTable Tools tab are enabled, which is not the case for regular PivotTables built off of an Excel range. These tools allow you to create Excel cube functions that retrieve data from your range data. You can convert your PivotTable using the Convert to Formulas option. This video shows how easy this is.

Excel CUBEVALUE function that returns no data causes #VALUE error in downstream calculations

In an Excel Cube Formula report a CUBEVALUE function may sometimes legitimately return no data for that intersection in the database (that product was not sold in that region during that time period). A calculation in the worksheet that refers to the blank CUBEVALUE cell involving +, -, *, / and some other calculations will likely return a #VALUE error. This occurs because the apparently blank CUBEVALUE cell is actually returning a zero-length text value, which causes many numerical calculations to fail. Wrapping your CUBEVALUE functions with IFERROR() that returns zero on error is one way to fix the problem. But there is a somewhat more elegant approach for most situations, that uses a little-known Excel function, N().

The function tool tip describes N as:
Converts non-number value to a number, dates to serial numbers, TRUE to 1, anything else to 0 (zero).
Depending on how many downstream calculations you have pointing to your CUBEVALUE functions you can decide if it is easier to wrap the CUBEVALUE functions in N functions or (if there are a small number of downstream calculations) wrap the components of the cell calculations that reference a CUBEVALUE function. Watch this 3 minute video for more explanation.

Locating specific words in a worksheet or table

Track down all cells that contain a specific word or phrase with conditional formatting. Much easier than using Find. Also filter out a list of products, for example, that contain the word "blue" somewhere in the description. Video

Manipulating text in Excel - 2 basic functions

If you've used Excel mostly to manipulate numerical data, but have never used it to manipulate text, watch this video. It demonstrates 2 basic, but useful text functions: Left and Right.

Using PivotTables (Excel 2007/2010) to retrieve tabular cube data

This video is for people who access Microsoft Analysis Service cubes (sometimes called SSAS cubes) using a PivotTable. It specifically describes how to retrieve detailed data in tabular form that might then be saved as a CSV file, say, perhaps for import to another system.

SSAS cubes often have dimensions with user-defined (multilevel) hierarchies, such as time (years/quarters/months/days), territory (region/country/province), and so on. Negotiating these hierarchies to get the level you want, and no other levels, can be tricky. Also, large table extracts can be slow to manipulate as you select the dimensions and members you want. So it helps to do the right steps in the right order to minimize the time taken. Video

Quick copying (autofill)

Copying a cell down a column needn't be a drag. See why ...

How to copy Excel subtotals without the detail rows

If you have created subtotals using Excel's Data-Subtotals function and then tried to copy just the subtotals to another place you know that Excel copies the detail rows too. If that's not what you want here is how to fix it. Video

Quick chart to visualize data

Create a chart with one key press. Video

Advanced Filter: Blanks and Non-Blanks

How to show just blanks or non-blanks with Advanced Filter. Video



© 2011-17 Excelcraft.com

Home            Terms of Use            Privacy            Cookies            Contact