##
**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.

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