Book Read Free

Excel Tables

Page 17

by Zack Barresse


  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

 

‹ Prev