#### Archive

Category Archives for "Tables"
13

## How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows

Bottom line: This quick tip will save you a lot of time when deleting rows from a filtered range or table in Excel.  Learn how to prevent Excel from freezing or crashing when deleting rows.

Skill level: Beginner

17

## How To Unpivot in Excel with Power Query + Video Tutorial

Bottom line: Learn how to use Power Query to unpivot or normalize your data to get it ready for use in a pivot table.

Skill level: Intermediate

58

## Drop-down List Arrow Always Visible for Data Validation

Bottom Line: The drop-down arrow (icon) for a data validation list disappears when another cell is selected.  This technique will make the drop-down arrow permanently visible on the worksheet, even if the user selects a different cell.

Skill Level: Intermediate

88

## How to Create Dependent Cascading Drop-down Lists

Bottom line: Learn how to create cascading or dependent drop-down lists (also known as cascading validation) in Excel.  This technique does NOT require named ranges.  If you don’t mind using named ranges then there are a few links at the bottom of the page with solutions that will be easier to implement.

Skill level: Intermediate

Functions used: OFFSET, MATCH, COUNTA, COUNTIF, INDIRECT Continue reading

24

## Convert Pivot Table to SUMIFS Formulas – Free VBA Macro

This post contains a free VBA Macro that will convert any pivot table into a report containing SUMIFS, COUNTIFS, or AVERAGEIFS formulas.  If you want a very quick way to create a long SUMIFS formula, this is your ticket. 🙂

6

## How To Sum All Columns in the Total Row of an Excel Table

In this post and video I explain how to add the sum (subtotal) to all the cells in the Total Row of an Excel Table.

33

## Absolute Reference Add-in – Use the F4 Key in Structured Table Formulas

This post explains how to quickly create absolute references for table formulas using the Absolute References Add-in. This add-in will save you time when writing formulas with structured references (table syntax), and it’s FREE!

50

## Excel Tables Tutorial Video – Beginners Guide for Windows & Mac

#### The following video tutorial is an in-depth look at Excel Tables.

I show you how to create a Table and then explain 10 awesome features that will save you lots of time when working with your data.  The Tables feature is available in Excel 2007, 2010, 2013, and 2011 for Mac.  This video is a step-by-step guide and the file I work with in the video can be downloaded below.

_

135

## Absolute Structured References in Excel Table Formulas

This post will explain a trick for creating absolute structured references in Excel Table formulas.

Structured Reference Tables are great for creating clean, easy to read formulas.  But creating absolute references to the columns (aka anchoring the columns) in the formula is a bit tricky.

### Quick Guide

Duplicate the column references as if referring to multiple columns.  Absolute references to:

• One column in the same or other table: table1[[column1]:[column1]]
• One cell in the same row as the formula: table1[@[column1]:[column1]]
• Table names must be used even if the reference and formula cell are in the same table.
• You must drag these formulas across columns to maintain the absolute reference (copy & paste does not work).

I have developed an add-in allows you to use the F4 key on the keyboard to create absolute/relative references.  You can download the Absolute Reference Add-in here.