Glossary

Microsoft Excel is a spreadsheet application developed by Microsoft. It consists of rows and columns that form a grid, enabling users to input, organise, and analyse data. The program has a wide range of uses that span many industries and professions, but it’s commonly associated with finance and accounting activities.

Power Query is a tool for transforming and preparing data in Microsoft Excel and Power BI. Users can construct automated routines through a set of steps, which may include tasks such as filtering, creating custom columns, or changing data types.

Power Pivot is an optional Microsoft Excel add-in and integrated feature in Power BI, which is used to carry out advanced data analysis and build complex data models. The formula expression language DAX can perform calculations on large volumes of data that worksheets can’t due to their one million-row limitation.

Power BI is an interactive data visualisation and business intelligence tool developed by Microsoft. It’s used to build dashboards that showcase powerful insights, helping drive better business decisions.

Financial modelling is the process of summarising a company’s financial situation and using historical data to predict future performance. Common model types include 3-statement, discounted cash flow (DCF), and dividend discount.

A PivotTable is a Microsoft Excel feature that allows users to summarise large amounts of data by aggregating the values, making it easier to analyse. PivotTables are customisable, so the chosen fields can be arranged to return the desired layout. Like regular tables, they also have sort and filter capabilities.

A PivotChart is a Microsoft Excel feature that displays a visual representation of data. PivotCharts are similar to regular charts, except they are linked to PivotTables instead of worksheet cells. They also don't support scatter, stock, or bubble charts.

VBA is a programming language for automating repetitive tasks in Microsoft Excel and other Office applications. The macro recorder generates VBA code based on a user's actions, which is saved for the user to run when desired. Alternatively, manual code can be written to construct more complex procedures.

Office Scripts is a programming language for automating repetitive tasks in Excel for the web. Scripts are written in TypeScript, which is based on JavaScript. The built-in Action Recorder generates a script by interpreting a user's actions, such as editing cells, changing formatting or creating tables. Scripts can also be written manually for greater flexibility.

An add-in is an additional feature that extends Microsoft Excel's functionality. Add-ins like Solver and Analysis ToolPak are built in but must be activated first. Other official and third-party ones can be downloaded from the Office Add-ins Store.

A dashboard is a graphical user interface (GUI) that uses visual elements to summarise the most important details and display key performance indicators (KPIs) to track progress.

Power Automate is a Microsoft low-code tool for building automated workflows — it's part of the Power Platform. Users can create routines to web scrape, batch rename files, and trigger notifications, amongst many others.

DAX is a formula expression language used in Power BI and Power Pivot in Excel. Users can perform advanced calculations and queries on data by creating columns, measures, and custom tables.

A dynamic array expands or contracts depending on the number of values returned by a formula. Before 2020, Excel was based on one formula–one value. Along with the calculation engine change, a wealth of new functions arrived, enabling users to create powerful formulas that achieve considerably more with less.

Dirty/rogue data is a slang term to describe raw data of poor quality, whether it's inaccurate, incomplete, or inconsistent. It might contain spelling errors, duplicate values, or different formats used in the same column.

The Microsoft Office Specialist/Expert (MOS/MOE) exam is a proctored test demonstrating a person's knowledge of a specific Office product, such as Excel, Word, or PowerPoint. The MOS and MOE exams cover core and advanced material, respectively. Passing either earns the individual accredited status.

M is a 'mashup' query language featured in Power Query. Code is generated automatically as the user applies transformations that append to the applied steps. It can be edited or written from scratch for more advanced needs.