Office 365 Home Premium
Office 365 Small Business Premium
Office 365 Midsize Business
Office 365 Enterprise E3 and E4 (Enterprise and Government)
Office 365 Education A3 and A4
Office 365 ProPlus
Office 365 University
Office 365 Trial Subscription
The Office 365 ProPlus subscription includes a single desktop installation. All other subscriptions include five desktop installations, which can be either Windows or Mac versions.
NOTE
An Office Mobile installation does not count against the allotted number of desktop installations.
When you run the Office 365 application, it presents a login screen, as shown in the next figure.
Office 365 login screen.
You get a message like the one shown in the next figure when your Office 365 login is successful.
Confirmation of successful Office 365 login.
Once you're logged in to Office 365, you can connect to various cloud-based storage locations, including:
OneDrive (still listed as SkyDrive in version 1.1)
Office 365 SharePoint
SharePoint
For each location, you must enter additional login credentials. Once you're connected to a service, that service is listed in the PLACES view that appears when you click the Open button at the bottom of the application. The next figure shows Office connected to three of Zack's locations.
Office 365–connected places.
Unfortunately, Office Mobile severely lacks a lot of the functionality of most advanced features that are part of the Windows version. Office Mobile has Tables but without the Table style formatting because this version treats the data as a standard Excel range. Office Mobile supports basic filtering as well as an AutoSum feature that is similar to the status bar aggregation feature of the Windows version (see the next two figures).
Filtering in Office Mobile.
AutoSum in Office Mobile.
Tables in Excel for iPad
As with Office Mobile, the iPad version is available as a free download, and you can use the free version to view files. However, to edit files, you must have one of the following Office 365 subscriptions:
Office 365 Home
Office 365 Small Business Premium
Office 365 Midsize Business
Office 365 E3 and E4
Office 365 Education A3 and A4
Office 365 ProPlus
Office 365 University
Office 365 Personal NOTE
You must have iOS 7.0 or later to use Excel for iPad.
The iPad version is a complete rewrite of the Excel application, and therefore there isn't exact parity between the Windows version and the iPad version. Bill Jelen, aka Mr. Excel, has maintained an extensive list of the similarities and differences on his blog (www.mrexcel.com/learnexcel/); search for "Excel for iPad."
Once you're connected to an Office 365 account, click the New button to create a new workbook. You see a view that presents a number of templates, as shown in the next figure. You can choose one of them or choose to create a blank workbook.
The templates view that appears when you create a new spreadsheet in the iPad version.
Unlike Office Mobile, which doesn't show Tables at all, the iPad version does have Table functionality, but not quite as much as the Windows version. The ribbon supports contextual tabs, and the TABLE tab appears only when a cell within a Table is selected. As shown in the next figure, the TABLE tab includes these options:
Style Options—Contains options to show/hide the header and total rows, band rows/columns, and apply first and last column style options.
Table Styles—Displays the same 61 built-in styles as the Windows version, along with any custom Table styles.
Insert—Inserts columns to the left or right of the selection or selects rows above or below the selection.
Delete—Deletes the selected columns or rows.
Convert To Range—Removes the Table and leaves the data in a normal range of cells.
TABLE ribbon tab in the iPad version.
CAUTION
When you use Convert To Range, the iPad version does not display a verification prompt as the Windows version does. The easiest way to tell if your data is not in a Table is that the TABLE ribbon tab is not visible.
Similarities Between the iPad and Windows Versions
In terms of working with Tables, the iPad and Windows versions have some similarities and differences. Here are some of the most substantial similarities:
Auto expand Table—When you enter data in the first blank column on the right of a Table, the iPad version automatically extends the Table whenever it can.
Total row functions—The same lists of functions are displayed for the total row.
Structured referencing—The same syntax is supported.
Calculated columns—When you enter a formula into a Table cell, the formula is automatically filled into the entire column.
Duplicate column headers—When you enter a duplicate column header name, the rightmost of the duplicates is automatically modified so that the names are unique.
Differences Between the iPad and Windows Versions
Here are some of the most substantial differences in working with Tables in the iPad and Windows versions:
IntelliSense with structured references—The iPad version supports structured referencing when you enter formulas in a Table, but it does not support IntelliSense.
Insert below/right—In the iPad version, tapping on a single cell, regardless of its location in the Table, only gives options to insert rows above the selection or insert columns to the left of the selection. To insert rows below the selection or columns to the right of the selection, use the TABLE ribbon's Insert menu.
Select entire row/column—Because you use the iPad version with no mouse or keyboard, selecting entire rows or columns of a Table is slightly different. You tap and hold the first cell and then drag to the end.
Entering formulas in multiple cells—When you're working with the Windows version, if a formula is entered with multiple cells selected, pressing CTRL+ENTER enters the formula into the entire selection, whereas pressing ENTER enters the formula in the active cell only. In the iPad version, entering a formula with multiple cells selected puts the formula in all the selected cells. NOTE
Microsoft makes new iPad versions available, as it does with Office Mobile and Excel Online, with greater frequency than it offers new desktop versions.
Resources
Many resources offer information and support for Excel users and developers. Most of these resources offer general Excel-related information, and some focus on specific areas. Here we have listed Excel resources in four categories: books, blogs, forums, and websites. This list is not all inclusive, but it's a great start for those desiring to learn more about how to leverage Excel.
We have no financial interest in any of these tools and sites. These recommendations are based on our own usage of Excel, help found online, and tools available.
Books
Excel 2013 In Depth, by Bill Jelen: www.amazon.com/Excel-2013-Depth-Bill-Jelen/dp/0789748576/
Excel 2013 VBA and Macros (MrExcel Library), by Bill Jelen: www.amazon.com/Excel-2013-Macros-MrExcel-Library/dp/0789748614/
Excel 2013 Bible, by John Walkenbach: www.amazon.com/Excel-2013-Bible/dp/1118490363/
DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX, by Rob Collie: www.amazon.com/DAX-Formulas-PowerPivot-Excel-Mastering/dp/1615470158/
Microsoft Excel 2013 Step by Step, by Curtis Frye: www.amazon.com/Microsoft-Exce
l-2013-Step-By/dp/0735681015/
Ctrl + Shift + Enter, by Mike Girvin: www.amazon.com/Ctrl-Shift-Enter-Mastering-Formulas/dp/1615470077/
Excel 2013: The Missing Manual, by Matthew MacDonald: www.amazon.com/Excel-2013-The-Missing-Manual/dp/144935727X/
Don't Fear the Spreadsheet: A Beginner's Guide to Overcoming Excel's Frustrations, by Tyler Nash, Bill Jelen, Kevin Jones, and Tom Urtis: www.amazon.com/Dont-Fear-Spreadsheet-Frustrations-ebook/dp/B008CISAKO/
Blogs
Blogs are useful for tracking current events such as new releases and new features. Blog posts often provide thoughtful and focused presentations of various topics. Blogs usually offer a subscription service that delivers new posts directly to your email inbox when they are published.
colinlegg.wordpress.com
www.smittypro.com/Blog/
www.excelguru.ca/blog/
fastexcel.wordpress.com
blog.contextures.com
www.teylyn.com
dailydoseofexcel.com
exceluser.com/blog/
www.excelhero.com/blog/
chandoo.org/wp/
blogs.office.com/b/microsoft-excel/
msmvps.com/blogs/nateoliver/default.aspx
excelandaccess.wordpress.com
www.siddharthrout.com/my-blog/
Forums
Forums are great sources of help to many users. Experts support users, and uses support each other when they need assistance. If you really want to become an expert in a particular topic such as Excel, helping others on one of these forums is one of the best ways to learn by solving problems and watching other experts answer questions. Both Zack and Kevin learned much of what they know today by collaborating with other experts on forums while helping users solve Excel and other problems. Except where noted, there is no cost to sign up for a forum and ask questions once you provide a valid email address.
www.mrexcel.com/forum/forum.php
www.vbaexpress.com/forum/
www.excelguru.ca/forums/forum.php
answers.microsoft.com
forums.techguy.org
www.experts-exchange.com (This forum requires a small monthly fee to ask questions. Experts who earn a few points by answering questions each month can ask questions without paying the fee.)
www.tek-tips.com
www.thecodecage.com
www.xtremevbtalk.com
Websites
Some Microsoft Excel MVPs have their own websites where they publish articles, blogs, tools, and other interesting resources. Below is a list of some of the most popular websites for current MVPs, with brief notes on their specialty areas.
Andy Pope: www.andypope.info (charting, VBA, tips)
Bob Phillips: www.xldynamic.com/source/xld.html (formulas, VBA, dynamic ranges)
Charles Williams: www.decisionmodels.com (FastExcel, formula optimization)
Charley Kyd: www.exceluser.com (charting, dashboards, a list of past/present Excel MVP websites)
Chip Pearson: www.cpearson.com/Excel/MainPage.aspx (anything Excel, COM development)
Daniel Ferry: www.excelhero.com (coursework, formulas, VBA, development)
Dominic: www.xl-central.com (formulas, VBA, mega-formulas, videos)
Debra Dalgleish: www.contextures.com (anything Excel)
Jan Karel Pieterse: www.jkp-ads.com (VBA, defined names, troubleshooting)
Jon Acampora: www.excelcampus.com (charting, formulas, videos, formatting)
Jon Peltier: peltiertech.com (charting, add-ins, dashboards)
Ken Puls: www.excelguru.ca (customizing the ribbon, VBA, formulas, BI)
Ron de Bruin: rondebruin.nl (VBA, customizing the ribbon, Mac/AppleScript)
Tom Urtis: www.atlaspm.com (VBA, filters, formulas)
Index
Symbols
@
versus This Row 16
.NET
Code smaples 127
#REF! errors 15
Header disabled 37
@ versus This Row 39
A
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
B
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
C
C#
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
D
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
E
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
F
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
H
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
I
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
K
Keeping filters
While hidding buttons 33
L
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