ExcelTables-13

Index

Symbols

versus This Row 16

.NET

Code smaples 127

#REF! errors 15

Header disabled 37

@ versus This Row 39

Absolute reference

Contrasted 40

Simulating 39

Acampora, Jon 3

Access database 87

Accessibility 14

Active Property 99

Adding a column

with VBA 106

Adding a ListObject 105

Adding a row

with VBA 106

ADOMD.NET 52

Alternative Text 14

AlternativeText property 99

AMO 52

Appending

Preventing 23

Array formulas

and relative references 40

When allowed 10

AutoCorrect options 23

AutoFilter Property 99

Automatic resizing 22

Azure Marketplace 91

Backward compatibility 16

Banded columns 77

Banded rows 77

Double-high 81

Battagin, Dan 3

Books 149

Bugs

SUBTOTAL in 2013 37

Bulk insert

with VBA 120

Does a table exist 128

Calculated columns 9

CELLS object

in VBA 94

Circular reference

After moving 24

Clearing filters

with VBA 119

Collie, Rob 3, 56

Colors

Accent 83

Sorting by 65

Columns

Multiple 39

Comment Property 99

Companion website 3

Compatibility

backward 16

Connection strings 90

Convert to range 27

Copy and paste

and relative references 40

Copying formulas

Multi-column not relative 39, 41

Copy table style to a new workbook

with VBA 123

Creating filters

with VBA 118

Creating tables

from Ribbon 19

Using Ctrl+T 21

CTRL+L. 21

Custom sorting 66

Custom styles 78

Dalgleish, Debra 3

Data body range 7, 8

DataBodyRange 96

Data model 52

Data Model

Adding a table 53

Updating 54

Data validation 45

Date filters 68

DAX

Edit DAX 56

Table queries 54

Default styles 80, 84

Delete Method 104

Deleting a row

with VBA 106

Deleting rows 26

with VBA 115

Developer tab 93

DisplayName Property 100

Does a table eist

with VBA 113

Does a table exist

with C# 128

with VB 129

Does column exist

with VBA 114

Duplicates

Removing 28

Dynamic referencing 44

Edit DAX 30

Edit DAX dialog 56

Error message

from DAX 57

Escape character 44

Evaluate statement 56

Excel for iPad 146

Excel objects 93

Excel online 131

Filtering 135

Formulas 134

Reduce table functionality 133

Surveys 136

ExcelTables.com 17

ExportToVisio Method 104

External data 87

Refreshing 88

Fill handle

and relative references 40

Filter button 7, 14, 33

does not appear 14

Filter by Color 68

Filtering

with VBA 117

Filters 67

Filter State 69

for Dates 68

for Numbers 68

for Text 68

for Values 69

Removing 70

with Slicers 71

Filter state

with VBA 118

Find table address

with VBA 114

First column 32, 77

Formulas

as Header row 8

Creating 37

in Tables 35

Forums 149

Forward compatibility 16

Freeze panes 13

Header row

Formulas trick 8

in Formulas 37

Must be single row 10

Header Row 7

HeaderRowRange 96

Hiding dropdowns

with VBA 119

Hiding filters

While keeping filter 33

How many columns

with VBA 114

how many rows

with VBA 114

Hungarian notation 15

Implicit intersections 36

INDIRECT function 27

for Absolute 41

Insert filters dialog 71

Inserting rows 11, 24

versus Appending 12

InsertRowRange 97

IntelliSense 40

Intersections 36

Keeping filters

While hidding buttons 33

Last column 32, 77

Limitations

of Tables 10

List

versus Talbe 11

ListColumn Property 103

ListColumns 97

ListObject 96

ListObject Object’s Add Method 105

ListRow Property 104

ListRows 97

Lists 6

Live Preview 76

Looping through columns

with VBA 117

Looping through rows

with VBA 116

Mac Excel 141

Filtering 142

Marketplace data 87

M code 61

MDX 54

ME 95

Members 94

Merged cells prevent appending 13, 23

Microsoft Query 87

for Crosstab queries 90

Moving columns 24

Multiple columns 39

Name manager 45

Name Property 100

Naming Conventions 15

N function 47

Number filters 68

Object model 93

Object variables 95

OData 87

ODBC 87

Office Mobile 143

OFFSET function

for Absolute 41

OLEDB 52, 87

OneDrive 131

Options 32

Parent Property 100

PivotDiagram 30

PivotTables 49

Power BI 51

Power Pivot 51

with Excel 2013 53

Power Query 6, 57

Editor window 59

Renaming columns 59

Publish Method 104

Pwer Pivot data model

with VBA 111

Querying data 89

QueryTable Property 100

Range property 96

Reapplying filters

with VBA 119

Refreshing

External data 88

PivotTables 50

using VBA 50

Refresh Method 104

Remove duplicates 28

Repair formatting

with VBA 122

Resize Method 104

Resizing handle 22

Resizing table 10

Resources 148

Retrieving Crosstab query

with VBA 124

Row limits 52

Row number

Displaying 46

Running total 46

Sharepoint

Exporting to 28

Share Point

with Power Pivot 53

SharePointURL Property 101

ShowAutoFilter 97

ShowAutoFilterDropDown 97

ShowHeaders 97

ShowTableStyleColumnStripes 98

ShowTableStyleFirstColumn 99

ShowTableStyleLastColumn 99

ShowTableStyleRowStripes 99

ShowTotals 97

Sizing handle 9

SkyDrive 131

Slicers

Excel online 71

in Excel 2010 17

items with no data 71

Require header 73

Styles 72

Slicers Property 101

Sorting 65

by Color 65

Custom 66

State 67

Sort Property 102

SourceType Property 103

Special characters 43

SQL server

Connecting to 89

SQL Server 54, 87

SSAS 87

Stacking tables 12

Stripe size 81

Structured references 16, 35

Advantages 36

Operators 43

Syntax 35

Structured referneces

Disabling 42

Styles 63, 77

Copying to new workbook 85

for Cells 84

for Tables 75

Table elements 80

SUBTOTAL function 9

Bug in 2013 37

SUM function

to ignore headers 47

SUMIF function 41

Summary Property 103

Surveys 136

Syntax 35

Table

Limitations 10

Table name rules 14

TableObject Property 103

Tables 5

as PivotTable source 49

Convert to range 27

Creating 19

Defined 5

Formatting 75

Formulas 35

History 6

Parts of 7

Resizing 22

Unique name 10

versus List 11

TableStyle 98

Table styles 63

Table tools

in Ribbon 13

Testing

for Calculated column 9

Text files 87, 90

Theme colors 84

This row

versus @ 16, 39

ThisWorkbook 95

Timelines 73

Total row 8

Argument list 38

in Formulas 38

Must be single row 10

Toggling 32

TotalRowRange 96

Umlas, Bob 3

Unique headers 7

Unlink Method 105

Unlist Method 105

Unpivot 59

Validation 45

in Total row 38

Variables 95

Does a table exist 129

VBA 93

Active property 99

Adding a column 106

Adding a ListObject 105

Adding rows 115

AlternativeText property 99

AutoFilter method 107

AutoFilter property 99

Clearing filters 119

Comment property 99

Converting to table 111

Copy new table style to workbook 123

Creating a table 111

Creating filters 118

DataBodyRange 96

Delete Method 104

Deleting a table 107

Deleting rows 115

DisplayName property 100

Does column exist 114

Does table exist? 113

ExportToVisio Method 104

Filtering 117

Filter state 118

Find a table address 114

HeaderRowRange 96

How many columns 114

How many rows 114

Icon sets 108

InsertRowRange 97

ListColumn property 103

ListColumns 97

ListObject 96

ListObject Object’s Add Method 105

ListRow property 104

ListRows 97

Looping through columns 117

Looping through rows 116

Name property 100

Naming a table 111

Object variables 95

Parent property 100

Power Pivot data model 111

Publish Method 104

QueryTable property 100

Range property 96

Reapplying filters 119

Refreshing PivotTables 50

Refresh Method 104

Repair formatting 122

Resize Method 104

Retrieving new Crosstab query 124

SharePointURL property 101

ShowAutoFilter 97

ShowAutoFilterDropDown 97

ShowHeaders 97

ShowTableStyleColumnStripes 98

ShowTableStyleFirstColumn 99

ShowTableStyleLastColumn 99

ShowTableStyleRowStripes 99

ShowTotals 97

Slicers property 101

Sort property 102

SourceType property 103

Summary property 103

TableObject property 103

Table parts 110

TableStyle 98

TotalRowRange 96

Unlink Method 105

Unlist Method 105

with External data 112

XmlMap property 103

VertiPaq 52

Visio

Exporting to 30

VSTO 127

Web pages 87

Website

Companion 3

Websites 150

What-if tables 2

Wikstr, Max 3

Windows XP 54

XLSX file format

prevents VBA 51

XML data

with VBA 113

XML data import files 87

XML formatted files 17

XmlMap Property 103

xVelocity 52