| Excel term | Description |
|---|---|
| Activate | In Excel, activate can refer to whether a worksheet, or group of worksheets, is active, so that any actions will apply to that sheet / group. TOPIC PAGE: See how to activate a worksheet in our page all about managing worksheets. Activate is also a command in VBA (Visual Basic for Applications), which is the programming language for Excel and other Office programs. |
| Adjacent and non-adjacent cells | Excel worksheets are made up of a two-dimensional grid of cells. Adjacent cells in Excel are next to each other, either in the same row or column. They share a border. Non-adjacent cells in Excel don't sit next to each other, or share a border. They can be on the same worksheet, or on different worksheets. |
| Array | An Excel array is a collection of number or text values. An array can sit in a single row or column (a vector), or a two-dimensional range (otherwise referred to as a matrix). Array values usually sit in adjacent cells. The array structure is key to how Excel deals with array formulas. Arrays are always wrapped in curly brackets {}. |
| Array formula | In Excel, an array formula performs calculations on multiple items contained in an array. An array formula can return multiple values. For example, an array formula can be used on a two-column matrix to multiply values in Column A with Column B, and insert the results into Column C. Instead of entering the formula multiple times into Column C, the array formula is entered just once. Array formulas can also return a single value, e.g. to multiply the values in Column A with those in B and then sum the results to create a single total. See also Dynamic array formulas below. |
| Calculation options | Chances are you may not be aware of Excel's calculation options until you suddenly find that formulas are not calculating automatically. TOPIC PAGE: See our topic page on what to do if formulas are not automatically calculating in Excel. Find calculation options on the 'Formulas' section of Excel's ribbon -- options available are automatic, manual, and automatic except for data tables. |
| csv file format | CSV stands for 'Comma Separated Value'. The csv file format is used by spreadsheet and database software such as Microsoft Excel and Access. Data is stored in table form (organised into rows and columns, where a row is a single record). The values are separated by a comma, saved as plain text (no formatting such as font style or weight can be stored with the value). csv files are a good way for software to import and export data because the data is simplified enough to cross software platforms, but still contain enough meaningful information to share effectively. The text is said to be 'delimited' because it is separated by a character -- in this case a comma. Excel also makes use of 'delimiters', e.g. when you import data into an Excel file you can select how the data is loaded into the grid of cells in the worksheet, separating the blocks of data by tabs, spaces, etc. |
| Data validation | Data validation in Excel is where you limit the type, length, and values of data that can be entered into a cell. Examples of types of data are date, time, whole number. Values can be limited e.g. to 'less than', 'greater than'. Data validation is also used to create dropdown lists in Excel -- see the example here: Create a dropdown list Data validation options are found on the data tab | Data tools section on Excel's ribbon. |
| Dynamic array formulas | In September 2018, Microsoft added a new feature to Excel, dynamic array formulas. A dynamic array formula in Excel automatically fills a cell range, according to the number of values generated by the array formula. In older versions of Excel, the user needed to select the correct range of cells, then press Ctrl+Shift+Enter (CSE) to activate the formula. TOPIC PAGE: See how to use an Excel dynamic array formula to generate a dynamic list of latest values from a data table. |
| Evaluate Formula | Excel has an inbuilt command 'Evaluate Formula' which steps through a nested formula to find where an error is. TOPIC PAGE: See our topic page on how to use the Evaluate Formula command. |
| Formula | A calculation or sequence of commands in a cell. Formulae must begin with an = symbol for Excel to recognise the cell contents as a formula. E.g., an Excel formula might be to add two other cells together, or it could be to check if another cell contains specific text and take different actions according to the result. |
| Formula bar | Excel's formula bar is the long field just below the ribbon, which displays the content of the currently active cell. |
| Function | An inbuilt command. Common functions are simple calculations: SUM, AVERAGE, MEDIAN; and basic commands such as IF, LOOKUP, MATCH. There are approximately 460 functions in Excel 2016, so you could learn one a day for a year, and still have some way to go! |
| IFERROR | Excel's IFERROR function enables you to capture and customize the results from IF statements that might return errors in some conditions. TOPIC PAGE: See our topic page on using IFERROR, with examples of customizing different results, including IFERROR then BLANK. |
| Lookups | Use Excel's lookup functions to find values in a cell range. For example, you can use the VLOOKUP() function to find a target value based on a known row and column header value. TOPIC PAGE: See our topic page on a two-way lookup using VLOOKUP and MATCH. |
| Power Query | Power Query is Excel's suite of data connection and analysis functions that enable you to connect to, inspect, combine, clean, manipulate and analyse data. Power Query is one of the foundation stones of Microsoft's Power BI. TOPIC PAGE: See how to get started with Power Query, connect to data, and add an Index column. Power Query is a standard set of functions in Excel 2019, and is found on the Data tab of the ribbon ([July 2019] currently named 'Get & Transform Data' and 'Queries & Connections'). Microsoft does fairly regularly revamp the way the Power Query commands and options are arranged and named on the ribbon, so in earlier versions of Excel 2019 and Excel 2016 the commands will look a bit different. Previously Power Query came as an add-on. |
| Table | An Excel table is created by using the 'Format as Table' on a selected range of cells. The power of Excel tables includes having header rows, calculated columns, total rows, and can be easily resized. Excel tables expand automatically when new rows are added. TOPIC PAGE: See our topic page on Excel tables, showing how to create, format, rename, and resize tables. |
| Wide and long data | Data tables are generally referred to as either wide or long, and this is to do with the structure of how the variables (e.g. age, height, weight) are laid out with the values. A wide (stacked, grouped by categories) data table contains each variable in a different column. Each row relates to one 'record' or observation. E.g. a table with a customer address which has separate columns for each element of the address. A long data table has all the values in one column, and another column(s) giving the context of the values. In this structure, there are likely to be duplicate values in the main object column (e.g. Customer), so an Index column becomes essential to identify individual records (rows) in the table. Excel's pivot functions enable re-structuring of data tables. Statistical packages (such as R or SPSS) prefer one type over the other, and contain functions which enable converting long to wide or vice versa. |
Workbook | Also called a file or document which contains all the pages (worksheets). Excel 2016 uses the file extension .xlsx for a standard filename, e.g. myworkbook.xlsx |
| Worksheet | This is a page within a workbook. A new Excel workbook opens with 1 sheet by default (Sheet1). Sheets can be renamed, and added by clicking on the + sign next to the last sheet. Worksheets are also referred to as tabs. An Excel file saved as a csv (comma separate values) can only have one sheet and all data is saved without any formatting. The worksheet is saved with a shortened version of the filename by default. TOPIC PAGE: See how to activate a worksheet in our page all about managing worksheets. |