Range and Tables Excel

Copy and Paste multiple items in Excel

Copy multiple items to the Excel Clipboard and paste them selectively one or multiple copy-paste at once.

Windows stores the information on the Windows Clipboard, which is an area of your computer’s memory. Each time that you cut or copy text, Windows replaces the data previously stored on the Clipboard with the new information that you cut or copied.

Microsoft Office has its Clipboard (the Office Clipboard), which is available only in Office programs.

Not acceptable rule to Name Range

Excel provides several methods that you can use to create range names. Although specific rules and guidelines are to be followed before naming a range:

    • AnswerRange Names can’t contain Space like ‘Year 2016’.
    • Symbols aren’t allowed except for underscores (_), backslashes (\), and periods (—).
    • You can use any combination of letters and numbers, but the name must begin with a letter, underscore, or backslash.
    • Neither a reference looks like a cell address like A2, Y16 or CY16
    • Names are not case sensitive. For Excel, ‘Year_2016’ and ‘year_2016’ are the same.
    • Names can incorporate up to 255 characters.

Create from Selection in Name Range

The Name Box or New Name can only help in creating one name range at a time.

Excel provides an ability to name several adjacent cells or ranges at once using row labels, column labels, or both just by clicking the Create from Selection button within the defined names Group.

Assign multiple Named Ranges in Excel
Quickly assign multiple Named Ranges with labels in selected data range

Delete and Edit a Naming of range

You can use the Name Manager dialog box that not only creates a new name but also edits or delete existing ranged names, and ­even alters the list of names.

Name Manager provide ability to manage all defined names in the Excel workbook
Name Manager provide ability to manage all defined names in the workbook

Deleting a Name used in a Excel Formula

If a name is part of a formula, deleting the name (Editing is fine) causes the formula to become an invalid name. (NAME? error) It seems logical that Excel would replace the name with its actual address, but that doesn’t happen. You can click Undo from the Quick Access toolbar (or press Ctrl+Z) to get the name back.

You have to update again the cell reference link with the formula to replace Name?. error.

Keyboard Shortcut to Create Excel Table

The shortcut key to create an Excel table is to click any cell within your data list and then press  Ctrl + T or Ctrl + L

Excel Table is a way of data presentation in a cross-tabular format where each column contains a different category and row representing the respective set of values or vice versa.

Excel table makes data sets accessible to style, manage (add or delete row and column) and perform data summary functions.

Note: Excel may not guess the table’s dimensions correctly if the table isn’t separated from other information by at least one empty row or column. 

Convert a data range to an Excel table
Convert a range of data or list to an Excel table

Summarize data within a Table

One great way to summarize data within a table by Total Row (Alt+JT+T) box check.

Like if you want to add all numbers in a column, we can use this option, it adds the total row and give the option for other formulas automatically.

Click Total Row and summarize data in Excel Table
Click 'Total Row' and find average of our sales

The SUBTOTAL function is one of two functions that ignore data hidden by filtering. The function adjusts its return value dynamically depending upon the row is hidden or visible.

Filter Excel Table using Slicer

You can filter a table using Slicer in Excel. Slicers are very visual, and it’s easy to see exactly what type of filtering is in effect.

Slicer contains a button for every unique item to filter Excel Table
Slicer contains a button for every unique item in the field

Not correct for Excel Table

  • After applying the filter, only the visible data is copied, not the entire table.
  • Clear only delete the Table format, but the list is still an Excel table.
  • Answer– Excel Table format will not override other formats like cell color, conditional formatting, font, etc. 
  • Custom table styles are available only in the workbook in which they were created.
    However, if you copy a table that uses a custom style to a different workbook, the custom style will be available in the other workbook.

Curly braces “{ }” represent Array Formula

In simple terms, an Array is a collection of Items.

While entering the Array formula you need to press Ctrl+Shift+Enter (Excel add curly braces “{ }” ).

Not valid for Multi-cell array formula

A Multi-cell array returns the result in a range of cells.

  • Return identical formula for a range of cells.
  • AnswerCan not edit part of a multi-cell array formula.
  • Difficult to tamper a formula
  • We need to update the formula range selection to accommodate new data.

Two Dimensional Array is separated

two-dimensional array uses semicolons (;) to separate its vertical elements and commas (,) to separate its horizontal elements (depending on Excel’s language).

For separation, Semicolons (;) for columns and commas (,) for a row.