What keyboard shortcut can you use to turn on or turn off a total row at the end of the table?
|
The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row. Show
OverviewOne of Microsoft’s greatest gifts of Excel 2007 was the introduction of the table feature. It allows us to convert an ordinary range into a table (Insert > Table, or, Ctrl+T). Once the data is stored in a table, we can use the table’s name in our formulas. Since tables auto-expand to include new rows, new transactions are automatically included in our formulas. This auto-expansion property helps us build more reliable workbooks. Another special property is the total row. The total row can be toggled on, or off, at any time to show, or hide, table totals. There are a few interesting points to note about the total row, so, let’s dig into the details. Total RowIt is easy to turn the total row on and off. We just use the Total Row checkbox in the TableTools > Design ribbon tab, as shown below: Customize TotalsWhen you turn on the total row, Excel does its best to determine which column you want to add. If Excel displays a total for a column that doesn’t need one, simply select the drop-down control in the total row and select none. The drop-down also contains other math functions, such as Average and Count. So, it is pretty easy to change the math used by the total row for any given column, as shown below: FiltersTables come with filter controls on the header row. The nice thing is that when you apply one of the filters, the total row updates. Specifically, it will include visible rows and exclude hidden ones. This makes it easy to quickly analyze the data, as shown below: Adding RowsEven with the total row on, it is easy to add new rows to the table. If you are going to manually key the new rows, just use the Tab key and when you get to the last column, Excel will automatically insert a new blank row for your entry, as shown below: If you are going to add a bunch of rows, you can resize the table by dragging the sizer control in the lower-right corner of the total row, as shown below: If you are going to paste rows, just be sure you paste them into the total row. Excel will push the total row down and insert the new rows accordingly, as shown below: The table feature is incredible, and I use it all the time. It improves efficiency and reduces errors…thanks Microsoft for this gift! If you have any other fun table tricks…please post a comment below! Additional ResourcesSample File: TotalRow How to Filter Microsoft Excel Data Quickly using Keyboard Shortcutsby Avantix Learning Team | Updated October 20, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) You can turn on filtering (formerly known as auto filtering) for Microsoft Excel lists and tables and easily filter and sort data using a mouse. When you first turn on filtering, arrows appear in the header row for each field with a drop-down menu. Although most users will use the mouse to apply and remove filtering, you can also use your keyboard. The shortcuts are available in Excel 2010 and later versions but some will also work in 2007. Recommended article: How to Enter Data in an Excel Filtered List into Visible Cells (2 Ways) Do you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses > The following are 10 useful keyboard shortcuts for filtering. 1. Turning filtering on or offTo turn filtering on or off, ensure a cell in the range is selected and then press Ctrl + Shift + L. Down arrows will appear beside field names in the header row as follows: If your data range contains any blank columns or rows, select the entire range of cells first. If you have converted a list to a table, the Filter menus should automatically appear. 2. Displaying the Filter menuTo display the Filter menu for a column:
 If your data has been converted to a table, you can press Alt + Shift + down arrow in any cell in the table to display the Filter menu for that column. 3. Selecting menu items using arrow keysOnce the Filter menu is displayed, you can use your arrow keys to navigate the menu and use Enter and the Spacebar to select and apply filtering:
4. Clearing all filtersTo clear all filters for all fields in the current filtered range, select a cell in the range and press Alt > A > C. Don't press Shift and don't press these keys at the same time as they access the Ribbon. Simply press Alt, then A, then C. You can also press Ctrl + Shift + L t turn filtering off which will remove the filters. Press Ctrl + Shift + L to turn filtering on again. 5. Clearing filters in a columnTo clear the filters in a column:
6. Filtering by typing underlined charactersOnce the Filter menu is displayed, you can type underlined letters to select a filter option. The underlined letters that appear in the menu are the shortcut keys for each command. For example, typing the letter "F" would display the Text, Number or Date Filters sub-menu. Don't press Shift while typing these characters. Note the underlined characters in the menu below: 7. Sorting by typing underlined charactersOnce the Filter menu is displayed, you can type characters to sort (do not hold down Shift):
8. Filtering using the Search boxStarting in Excel 2010, a Search box was added to the Filter menu. You can enter search criteria in the Search box and Excel will automatically filter in the column. To use the Search box:
Note the Search box in the menu below: 9. Displaying the Custom Filter dialog boxWhen you want to filter using custom criteria, you can display the Custom Filter dialog box:
Enter criteria in criteria boxes in the Custom Filter (AutoFilter) dialog box below: In the Custom Filter dialog box, you can keep pressing Tab to select different options in the dialog. 10. Filtering blanks or non-blanksTo filter to display blank cells in the selected column:
To filter to display non-blank cells in the selected column:
If you use filtering a lot, these keyboard shortcuts can save you quite a bit of time. Subscribe to get more articles like this oneDid you find this article helpful? If you would like to receive new articles, JOIN our email list. More resourcesHow to Convert Seconds to Minutes and Seconds in Excel 3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells How to Delete Blank Rows in Excel (5 Fast Ways to Remove Empty Rows) How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways) How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values Related coursesMicrosoft Excel: Intermediate / Advanced Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools Microsoft Excel: Introduction to Visual Basic for Applications (VBA) VIEW MORE COURSES > Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you. Copyright 2022 Avantix® Learning Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners. Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at How would you turn the Total row on and off using the keyboard?The Ctrl key+Shift key+ T key is used to hide or display the rows consisting of the total value in a table.
What is the shortcut key of row?Shift+Spacebar to select the row. Alt+I+R to add a new row above.
What is the shortcut key to hide the entire row?To hide rows or columns you just need to select cells in the rows or columns you want to hide, then press the Ctrl+9 or Ctrl+Shift+( shortcut. To unhide rows or columns you first need to select the cells that surround the rows or columns you want to unhide.
What is the shortcut key of table?Opening and closing tables and table menus. |
