K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Excel - My Favourite Things - Excel Tables

8/3/2021

 
There is rarely a workbook I use that does not contain at least one Excel Table. What is an Excel Table? An Excel Table is a range of related cells grouped to make managing and analyzing much easier. An Excel Table provides a reference to a self-expanding range of cells with a header row and filtering. In addition, an Excel Table delivers the following advantages over using just a range of cells: 

  • Excel Table name – a customizable name applied to the range of cells for easy reference and to include table cells and ranges within a formulas 
  • Excel Table formatting – providing quick and easy formatting of the data range 
  • Calculated columns – inputting a formula into a single cell will result in it being replicated to the other cells in the table column 
  • Total Row – provides a variety of calculations for a table column, including sum, count, average, and so on. 
To convert a range of related cells to an Excel Table, first make sure you have a header row (the first row containing the description of the column) and then with a cell in the range selected, click Format as Table on the Home tab.  
Picture

Clicking the command shows the Table formats available. Select the one you would like to use. Now confirm the range chosen by Excel is correct and that it has identified your first row has headers.
Picture

The range of related cells is now an Excel Table. Note the Table Design tab at the end of the ribbon. This tab is only available when you have selected a cell within the Table. The tab provides access to the Excel Table tools, including the place to change the table name, which I recommend doing.
Picture

To demonstrate one of the significant advantages of Excel Tables, we will summarize the data using the sumifs function. The sumifs function sums a range of cells based on up to 127 different criteria.
=sumifs(sum_range, criteria_range1, criteria1,….)
For our example, we will summarize the sales data by region. The Table has been renamed to Sales, and the resulting formula is as follows.
Picture

The sum range in the formula is the table name plus the column header in square brackets sales[Amount]. When typing the formula, the autocomplete will show the table name in the list of options. Then after entering the opening “[“ the list of headers is displayed in the autocomplete for selection.
Picture
Excel Tables make it much quicker and easier to input formulas correctly.

If you would like to bring your Excel skills to a new level with Excel Tables, please check out our webinar K2’s Excel Tables – Database Technology Comes to Spreadsheets – On Demand (2 hours)

Enjoy Excel Tables within your workbooks.

Ward Blatch, CPA CA

C ZED
8/6/2021 03:05:19 pm

Tables are good, but they suffer from a few oddities and inconsistencies. Such as inserting rows doesn't always copy the formula cells. I find writing larger formulas with field names cumbersome but we can swatch back to cell references.
There many other nits, so when I come across them I just revert back to named ranges.


Comments are closed.

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (905) 633-9772
© 2023 K2E Canada Inc. ALL RIGHTS RESERVED.