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. 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. |

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. |

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! |

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. 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. |

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 2019workbook 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. |