Basic Terms
Basic elements and features of a spreadsheet, such as cell, row, column, range, etc.
Active Cell: The cell that is currently selected and ready for data entry or modification. For example, when you click on a cell, it becomes the active cell.
Cell: The smallest unit of data in a spreadsheet, located at the intersection of a row and a column. For instance, you might enter a product's price in a single cell.
Cell Reference: The unique identifier for a cell, composed of the coordinates of intersecting column and row. For instance, the top-left cell is typically "A1".
Column: A vertical series of cells in a spreadsheet. For example, you might use a whole column to record dates.
Column Header: The identifying label at the top of a column, typically represented by alphabetical letters like A, B, C, etc.
Formula Bar: The area at the top of the spreadsheet where you can enter or edit data or formulas in the current cell. For example, you could type "=A1+B1" into the formula bar to add the values of cells A1 and B1.
Gridlines: The horizontal and vertical lines that separate the cells, rows, and columns.
Header Row: The first row in a spreadsheet typically used for column labels, such as "Date", "Sales", "Cost", etc.
Range: A block of cells that you have selected. It's often defined by the upper-left and lower-right cell references. For example, "A1:C3" is a range.
Row: A horizontal series of cells in a spreadsheet. For instance, you might use a row to enter all the data related to a specific client.
Row Header: The identifying label on the left of a row, typically represented by numbers like 1, 2, 3, etc.
Sheet: A single page within a spreadsheet file. For instance, you might use different sheets for different months in a budget spreadsheet.
Spreadsheet: A digital worksheet composed of cells arranged in rows and columns, used for organizing, analyzing, and storing data.
Workbook: A file containing one or more sheets in a spreadsheet program. For instance, an Excel file is a workbook.
Worksheet: Another name for a spreadsheet or sheet. It's a single page in a workbook.
Basic Spreadsheet Operations
Actions and manipulations that users can perform in a spreadsheet, such as sort, filter, cut, copy, paste, etc.
Autofill: A feature that automatically fills cells with data, such as a series of numbers, a list of days of the week, etc. For example, you could autofill a column with the months of the year.
Cell Formatting: The process of changing the appearance of a cell and its contents. This can include font style, background color, number format, etc. For instance, you might format cells to display currency.
Copy: The operation of duplicating the content of a cell or range of cells to be placed (pasted) elsewhere. For example, you can copy a formula from one cell and paste it into others.
Cut: The operation of removing the content of a cell or range of cells to be placed (pasted) elsewhere. Unlike copy, the original content is removed.
Delete: The operation of permanently removing data from a cell or range of cells.
Filter: A tool that allows you to show only rows in a list that meet certain criteria. For instance, you might filter a sales report to only see sales from a certain region.
Find and Replace: A tool that allows you to search for a specific term in a spreadsheet and replace it with another. For instance, you could use it to change a recurring product name.
Insert: The operation of adding new cells, rows, or columns to a spreadsheet.
Merge Cells: The operation of combining two or more cells into a single larger cell. This is often done for formatting purposes, such as creating a centered title over a table.
Paste: The operation of placing content that has been cut or copied into a new location in the spreadsheet.
Print Area: The specific region or regions of a worksheet that you designate to be printed when you print the worksheet. For example, you might set a print area to print a specific table in a larger sheet.
Sort: The operation of organizing data in a certain order, usually ascending or descending. For example, you might sort a list of employees alphabetically by last name.
Split Cells: The operation of dividing a cell into multiple cells. This is the reverse operation of "Merge Cells."
Undo/Redo: Actions that allow you to reverse (undo) or reapply (redo) recent operations in the spreadsheet.
Zoom In/Out: The operation of making the data in the spreadsheet appear larger (zoom in) or smaller (zoom out) for better viewing.
Advanced Spreadsheet Operations
Array Formula: A formula that performs multiple calculations on one or more of the items in an array. For instance, you can use an array formula to compute the sum product of two arrays.
Conditional Formatting: A feature that allows cells' formatting to change based on their contents. For instance, you might use conditional formatting to color cells red if they contain values below a certain threshold.
Data Validation: A feature that limits the type of data or the values that users can enter into a cell. For example, you can set a cell to only accept dates in a specific range.
Goal Seek: A tool that adjusts the value in a specified cell until a formula dependent on that cell returns your desired result. For instance, you might use Goal Seek to determine the necessary interest rate for a loan payment to be a certain amount.
Lookup Function: Functions used to search a specific value in a range of cells, and then return a value from the same position in a different range. Examples include VLOOKUP and HLOOKUP.
Macros: A sequence of instructions that automate complex tasks. For instance, you might create a macro that formats a report exactly to your company's standard.
Named Range: A descriptive name for a collection of cells or range in a worksheet. For example, you could name a range of cells "SalesData" for clarity in formulas.
Pivot Table: A data summarization tool that can automatically sort, count, total, or average the data stored in one table or spreadsheet. They are used for creating summarized views of data.
Relative & Absolute References: Relative references change when a formula is copied to another cell. Absolute references, designated by a dollar sign ($), remain constant, no matter where they are copied.
Solver: A Microsoft Excel add-in that can find optimal values for a formula in one cell, subject to constraints, or limits, on the values of other formula cells.
Subtotal: A built-in function that calculates subtotals if you have a list of data broken down by groups.
What-If Analysis: A process that allows you to change the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, scenarios and data tables are part of a suite of commands sometimes called what-if analysis tools.
Basic Formulas and Functions
Pre-defined instructions or commands that carry out specific calculations or operations, such as SUM, AVERAGE, VLOOKUP, etc.
AVERAGE: This function calculates the average (mean) of a group of numbers. For example, =AVERAGE(A1:A10)
calculates the average of the numbers in cells A1 through A10.
COUNT: This function counts the number of cells in a range that contain numbers. For example, =COUNT(A1:A10)
counts the number of cells with numbers in cells A1 through A10.
COUNTA: This function counts the number of cells in a range that are not empty, regardless of their content. For example, =COUNTA(A1:A10)
counts all cells in the range A1 through A10 that are not empty.
IF: This function performs a logical test and returns one value if the test is true, and another value if the test is false. For example, =IF(A1>0, "Positive", "Negative")
returns "Positive" if A1 is greater than 0 and "Negative" otherwise.
MAX: This function returns the maximum value in a set of values. For example, =MAX(A1:A10)
returns the highest number in the range A1 through A10.
MIN: This function returns the minimum value in a set of values. For example, =MIN(A1:A10)
returns the smallest number in the range A1 through A10.
SUM: This function adds up all the numbers in a range of cells. For example, =SUM(A1:A10)
adds all the numbers in cells A1 through A10.
VLOOKUP: This function searches the first column of a range for a key and returns the value of a specified cell in the row found. For example, =VLOOKUP("Apple", A1:B5, 2, FALSE)
searches for "Apple" in column A and returns the corresponding value from column B.
TODAY: This function returns the current date. The syntax is =TODAY()
.
CONCATENATE (or CONCAT in newer versions): This function combines two or more text strings into one text string. For example, =CONCATENATE("Hello ", "World!")
returns "Hello World!".
ROUND: This function rounds a number to a specified number of digits. For example, =ROUND(A1, 2)
rounds the number in cell A1 to two decimal places.
SQRT: This function returns the square root of a specified number. For example, =SQRT(16)
returns 4.
MEDIAN: This function returns the median (the middle number) in a set of numbers. For example, =MEDIAN(A1:A10)
returns the median of the numbers in cells A1 through A10.
Advanced Formulas and Functions
ARRAYFORMULA: This function enables the display of values returned from an array formula into multiple rows and/or columns. For example, =ARRAYFORMULA(A1:C3*B1:B3)
multiplies every cell from A1:C3 with the corresponding cell from B1:B3.
COUNTIF: This function counts the number of cells within a range that meet the given condition. For example, =COUNTIF(A1:A10,">20")
counts the number of cells in A1:A10 that contain numbers greater than 20.
HLOOKUP: This function searches for a value in the top row of a table or array of values and returns the value in the same column from a row you specify. For example, =HLOOKUP("Apple",A1:E5,3,FALSE)
looks for "Apple" in the first row and returns the value from the third row in the same column.
INDEX MATCH: A combination of the INDEX and MATCH functions. MATCH finds the position of a cell in a row or column, and INDEX returns the value in the cell at the intersection of a particular row and column. For example, =INDEX(A1:C3, MATCH("Apple", A1:A3, 0), 2)
returns the value in the second column of the row where "Apple" is found in A1:A3.
INDIRECT: This function returns the reference specified by a text string. For example, =INDIRECT("A1")
returns the value in cell A1.
IFERROR: This function returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. For example, =IFERROR(A1/B1, "Error encountered")
will return "Error encountered" if the division of A1 by B1 yields an error.
OFFSET: This function returns a cell or range reference that is a specific number of rows and columns from a given reference. For example, =OFFSET(A1, 1, 2)
returns the value of the cell one row down and two columns to the right of A1.
SUMIF: This function adds the cells specified by a given condition or criteria. For example, =SUMIF(A1:A10, ">20", B1:B10)
adds the numbers in B1:B10 corresponding to the cells in A1:A10 that are greater than 20.
SUMPRODUCT: This function multiplies corresponding components in the given arrays, and returns the sum of those products. For example, =SUMPRODUCT(A1:A3,B1:B3)
gives the sum of products of corresponding numbers in two arrays.
VLOOKUP with TRUE: When TRUE is used as the range_lookup value in VLOOKUP, it finds the largest value that is less than or equal to the lookup value. This assumes the first column in the table array is sorted in ascending order. For example, =VLOOKUP(900, A1:B5, 2, TRUE)
would find the closest match to 900 in a sorted column A and return the corresponding value from column B.
Basic Data Analysis
Terms related to analyzing and visualizing data, such as pivot tables, charts, conditional formatting, etc.
Bar Chart: A chart type that represents data in horizontal or vertical bars. It's used to compare quantities of different categories. For instance, you could use a bar chart to compare sales figures for different months or different products.
Cell Formatting: The ability to modify the appearance of cells, such as text color, font size, and cell color. For instance, you might use cell formatting to highlight cells that meet certain criteria or to make your data easier to read.
Conditional Formatting: This is a feature that changes the format of a cell based on its value. For instance, you can use conditional formatting to change the color of cells that contain values above a certain threshold.
Data Validation: This feature restricts the type of data or the values that users can enter into a cell. For example, you can set a cell to only accept numbers within a certain range.
Filter: A tool that allows you to display only the data that meets certain criteria. For instance, you might apply a filter to a column of dates to display only the dates within a certain month.
Line Chart: A chart type that uses a line to represent a series of data points connected by line segments. It's often used to visualize a trend in data over intervals of time – a time series.
Pivot Table: A feature that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet to obtain a desired report. For example, you might use a pivot table to summarize sales data by region.
Sort: A feature that allows you to arrange data in a certain order (e.g., ascending or descending). For instance, you might sort a list of employees by last name.
Pie Chart: A type of graph in which a circle is divided into sectors that each represent a proportion of the whole. It's often used to show percentage or proportional data.
Scatter Plot: A type of plot using Cartesian coordinates to display values for typically two variables for a set of data. It is used to plot data points on a horizontal and a vertical axis in order to show how much one variable is affected by another.
Advanced Data Analysis
Data Consolidation: A feature that allows you to combine data from several ranges into a single range. For example, you can use data consolidation to merge sales data from different regions into a single table.
Data Modeling: A method used to define and analyze data requirements needed to support business processes. In spreadsheets, this could involve the use of complex formulas, pivot tables, and various data sources.
Data Series: A row or column of numbers that are plotted in a chart. Each data series in a chart has a unique color or pattern.
Data Table: A range of cells in which you can change values in some cells and come up with different answers to a problem. They provide a shortcut for calculating multiple results in one operation.
Goal Seek: A tool that allows you to find the necessary input value to achieve a desired goal. It's a form of backward calculation from known result to unknown input. For example, you can use Goal Seek to determine the necessary interest rate for a certain loan payment.
Histogram: A graphical representation of data distribution in which a column contains the range of values, and the height of the column represents the frequency of occurrences within each range. This can be used to visualize the distribution of grades in a class, sales, etc.
Lookup Functions: Functions used to find specific information in a spreadsheet. While VLOOKUP and HLOOKUP are the most common, there are more advanced functions like INDEX MATCH and XLOOKUP in newer versions of Excel.
Regression Analysis: A statistical process for estimating the relationships among variables. It can help you understand how the typical value of the dependent variable changes when any one of the independent variables is varied. Excel can do this with the Analysis ToolPak.
Solver: An Excel tool that can find the optimal value for a formula in one cell, subject to constraints, or limits, on the values of other formula cells on a worksheet. A more advanced version of Goal Seek.
What-If Analysis: A process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Data Tables, and Goal Seek.
Basic Data Formatting
Terms related to the appearance of data in cells, such as font, color, cell formatting, etc.
Alignment: Determines the position of data within a cell. For instance, data can be aligned to the left, right, or centered.
Borders: Lines that can be applied to the outer edge of a cell or range of cells to help define boundaries and make data stand out. For example, a border could be placed around a total or sum cell.
Cell: The intersection of a row and column in a spreadsheet, where you can enter text, a number, or a formula.
Cell Formatting: The ability to change the look of individual cells. This includes setting the text color, background color, font size, cell borders, and more.
Fill Color: The background color that you apply to a cell to help text or numbers stand out. For example, you might use a different fill color for header cells.
Font: The design applied to a set of characters (numbers, letters, and symbols). In spreadsheet applications, you can change the font of the text in a cell.
Font Size: The size of the text in a cell, typically measured in points. Larger font sizes are used for headings and important information.
Font Style: Affects the shape of characters. Examples of font styles include bold, italic, and underline.
Format Painter: A tool that copies formatting from one cell to another. For example, if you've formatted a cell with a specific font size, font style, and cell color, you can use the Format Painter to apply that same formatting to another cell.
Number Format: Controls how numerical data is displayed, including the use of decimal places, whether a currency symbol is displayed, and how dates are formatted.
Row Height & Column Width: Determines how much vertical and horizontal space a row or column takes up. Changing row height or column width can make a spreadsheet easier to read.
Text Color: The color of the text within a cell. Changing the text color can make certain cells stand out.
Text Wrap: A feature that allows long text to flow into multiple lines within a cell.
Advanced Data Formatting
Conditional Formatting: A feature that changes the format of cells based on their contents. For instance, you can use conditional formatting to color-code cells that contain values above or below a certain threshold.
Custom Number Formatting: A tool that lets you create your own number format. For example, you could create a custom number format that displays numbers as percentages, fractions, dates, or even in a format specific to your business needs.
Data Bars: A type of conditional formatting that fills a cell with a gradient or solid color representing the relative value compared to other cells. The longer the data bar, the higher the value.
Data Validation: A feature that restricts what kind of data can be entered into a cell. For example, you might set up data validation to only allow numbers between 1 and 100.
Merge Cells: A feature that combines two or more adjacent cells into a single larger cell. This is often used when creating headers that span multiple columns.
Protect Cells: A feature that prevents specific cells from being changed. This is useful when you have cells with formulas that should not be modified.
Sparklines: Tiny, word-sized graphics that can be placed in a cell to provide a visual representation of data. For example, you can use a sparkline to show trends over time.
Styles and Themes: Predefined sets of colors, fonts, and effects that can be applied to a spreadsheet to quickly change its look and feel.
Subscript and Superscript: Features that let you reduce the size of text and move it below (subscript) or above (superscript) the baseline. This is often used in mathematical or scientific formulas.
Text Rotation: A tool that allows you to change the orientation of text in a cell. For instance, you can rotate text to display it vertically or diagonally, which can be useful for header rows.
Basic Error Types
Common errors that users might encounter while working with spreadsheets, like #DIV/0!, #VALUE!, etc.
#DIV/0! Error: Occurs when you divide a number by zero or an empty cell. For example, if you have a formula that divides cell A1 by cell B1, and B1 is empty or zero, you'll see this error.
#NAME? Error: This error occurs when Excel doesn't recognize text in a formula. It often happens when you mistype the name of a function, like typing "SUMM" instead of "SUM."
#N/A Error: Stands for "Not Available." This error is often seen when a function like VLOOKUP or MATCH can't find the value it's being asked to look for.
#NULL! Error: Occurs when you specify an intersection of two areas that do not intersect (cross). The intersection operator is a space character between references.
#NUM! Error: Occurs when a formula or function contains invalid numeric values. For example, if you attempt to calculate the square root of a negative number, you'll see this error.
#REF! Error: This error shows when a formula refers to a cell that’s not valid. This happens most often when a cell reference is deleted, or pasted over.
#VALUE! Error: Appears when a formula has the wrong type of argument or operand. For example, if you try to subtract a word from a number, you'll see this error.
Circular Reference: This isn't an error type per se, but it's a problem that can cause errors. A circular reference occurs when a formula refers to its own cell either directly or indirectly. For example, if you enter the formula "=A1+1" in cell A1, you have created a circular reference.
Advanced Error Types
Array Formula Errors: Array formulas can lead to a host of unique errors, often because they work with ranges of cells rather than individual cells. For example, if your array dimensions don't match (like adding two arrays of different sizes), you'll run into an error.
Data Type Mismatch Errors: This happens when you use the wrong type of data in a function that's expecting a different type. For example, a date function might return an error if it's given a text string that doesn't represent a date.
Data Validation Errors: If you have data validation set up in a cell and someone tries to enter something that doesn't match your validation rules, they'll see an error. For example, if you've set up a cell to only accept numbers between 1 and 100, and someone tries to enter 150, they'll get an error.
Lookup Errors: When you're using advanced lookup functions like INDEX MATCH or VLOOKUP with multiple criteria, there are numerous situations that might lead to errors. For instance, if the lookup value isn't in the first column of the lookup range for VLOOKUP, you'll get an error.
Nested Function Errors: Errors can occur when functions are nested within one another and something goes wrong in one of the inner functions. For example, you could be using a SUM function to add up all the values that a nested IF function returns, but if the IF function has an error, the SUM function will also return an error.
Reference Errors: More advanced reference errors can occur when working with things like named ranges, 3D references, or INDIRECT function. For example, if you've named a range and then try to use that name in a formula but mistype it, you'll get a #NAME? error.
Solver Errors: When using the solver tool to do advanced data analysis, several different errors can occur, like if the solver can't find a solution or if it runs into a constraint that can't be satisfied.
Statistical Function Errors: Errors can occur when using advanced statistical functions, often because the input data doesn't meet the function's requirements. For example, a linear regression function might return an error if there are fewer than two data points.
Basic File Management
Terms related to the management and organization of spreadsheet files, such as save, open, import, export, etc.
CSV (Comma Separated Values): A simple file format used to store tabular data, such as a spreadsheet or database. CSV files can be imported to and exported from spreadsheet software. For example, you might export your spreadsheet as a .csv file to share with someone who doesn't have the same spreadsheet software.
Export: The process of converting a spreadsheet or data set into a different format that can be used by other applications. For example, you might export a spreadsheet as a .pdf file for easier sharing and viewing.
Import: The process of bringing data from a different format or application into a spreadsheet. For example, you might import a .csv file into a spreadsheet to manipulate and analyze the data.
Open: The action of loading a spreadsheet file into the spreadsheet software for viewing or editing.
Save: The action of storing the current state of a spreadsheet to a file on a disk. For example, after making changes to a spreadsheet, you would save it to ensure the changes are not lost.
Save As: The action of saving the current spreadsheet under a new name or format. This leaves the original file unaffected and creates a new file with the changes.
Template: A pre-designed spreadsheet you can use as a starting point to create a new workbook quickly. Spreadsheet software often comes with a selection of templates for different purposes, such as budgeting, project management, and inventory tracking.
Workbook: An Excel file that contains one or more worksheets. You might manage your data within a workbook by separating different but related sets of data onto different worksheets.
Worksheet: A single spreadsheet within a workbook. A worksheet contains cells arranged in rows and columns, and it's where you do most of your work, like entering and formatting data, and performing calculations.
Advanced File Management
Data Connection: A link between a spreadsheet and an external data source, such as a database, a different spreadsheet, or a web page. With a data connection, the spreadsheet can display and interact with live data from the source.
Data Model: A feature in Excel that allows you to integrate data from multiple tables by creating relationships based on a common column. This is helpful in complex analysis tasks, where data from several sources must be combined.
Merge and Center: An Excel feature that combines multiple cells into one and centers the text. This is often used for creating headers that span multiple columns.
Power Query: A data connection technology in Excel that allows you to discover, connect, combine, and refine data across a wide variety of sources.
Protected View: A read-only mode in which most editing functions are disabled. This is often the default mode when you open a file that was downloaded from the internet or received as an email attachment, to protect you from potential security risks.
Track Changes: A feature that allows you to keep track of changes made to a spreadsheet, often used in a collaborative environment. It shows who made each change, what the change was, and when it was made.
Version History: Also known as version control, this feature allows you to see previous versions of the spreadsheet, and compare them to the current version or revert back to them if necessary. This is especially useful in a collaborative environment where multiple people are making changes to the spreadsheet.
XML (eXtensible Markup Language): A file format that structures data in a way that can be understood both by humans and by machines. Some spreadsheet software can import and export XML files, which allows for more complex data structures than CSV files.
Basic Collaboration and Sharing
Terms related to collaboration features in modern spreadsheet tools, like sharing, commenting, real-time editing, etc.
Collaboration: The ability for multiple people to work on the same spreadsheet simultaneously. For example, Google Sheets allows real-time collaboration where changes made by one person are instantly visible to others.
Commenting: A feature that allows users to add notes or queries to cells without altering the cell content. For example, you might use comments to ask a question or provide feedback about a specific piece of data.
Link Sharing: A feature that creates a URL for the spreadsheet, which can be sent to others for them to view or edit the spreadsheet. For example, you can turn on link sharing in Google Sheets to allow anyone with the link to access the sheet.
Permissions: The levels of access that different users have to a spreadsheet. For example, some users might have permission to view but not edit the spreadsheet, while others might have full edit permissions.
Real-time Editing: The ability for changes made to a spreadsheet to be instantly visible to all other users currently viewing the spreadsheet. This is a key feature of collaborative spreadsheet tools like Google Sheets and Microsoft Excel Online.
Revision History: A log of all changes made to a spreadsheet, often including who made each change and when. This is useful for tracking progress and for reverting unwanted changes.
Share: The action of giving other users access to a spreadsheet. In most spreadsheet software, you can share a spreadsheet with specific people, or generate a link that anyone can use to access the spreadsheet.
Version Control: The ability to view and revert to previous versions of a spreadsheet. This is useful in a collaborative environment where multiple people are making changes, as it allows you to see who made each change and to undo changes if necessary.
Advanced Collaboration and Sharing
Access Request: A feature that allows users without the necessary permissions to request access to view or edit a spreadsheet. The owner or managers of the spreadsheet will receive a notification and can then grant or deny the request.
Co-Authoring: An advanced form of collaboration in which multiple users can edit a spreadsheet simultaneously, with changes from all users visible in real-time. Co-authoring is a feature in some versions of Microsoft Excel.
Collaborative Filtering: A feature that allows each user to apply their own filters to a spreadsheet without affecting what other users see. This allows multiple users to analyze the same data in different ways at the same time.
Document Locking: A feature that prevents other users from making changes to a spreadsheet or specific parts of a spreadsheet. This can be useful when certain data needs to be protected from accidental changes.
Live Chat: A feature available in some collaborative spreadsheet tools that allows users to communicate in real-time within the spreadsheet interface. This is helpful for discussing changes and making decisions together.
Protected Ranges: Sections of a spreadsheet that can be 'locked' so only specific people can edit them. This is used to prevent accidental changes to important data.
Track Changes: An advanced feature in some spreadsheet software that logs all changes made to a spreadsheet. Unlike basic revision history, track changes can often show exactly what changed in each cell, and provide a more granular view of how the spreadsheet has been edited.
Workflow Automation: The use of scripts or third-party tools to automate parts of the collaboration process, such as sending notifications when changes are made, or automatically updating data based on certain triggers.
Spreadsheet Software-Specific Terms
Terms specific to different spreadsheet software, like Excel-specific terms, Google Sheets-specific terms, etc.
Microsoft Excel-Specific Terms
Excel Add-Ins: Programs that extend the capabilities of Microsoft Excel. For example, the Analysis ToolPak is an Excel add-in that provides data analysis tools for statistical and engineering analysis.
Pivot Table: A data summarization tool used in Excel. It can automatically sort, count, total, or average the data stored in one table or spreadsheet and create a second table displaying the summarized data.
Power Query: A business intelligence tool in Excel that allows you to connect, cleanse and shape data from a wide variety of sources.
Solver: An Excel add-in that can be used for what-if analysis. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.
VBA (Visual Basic for Applications): The programming language used in Excel to create macros. With VBA, you can automate repetitive tasks and extend Excel's capabilities.
Google Sheets-Specific Terms
ArrayFormula: A function in Google Sheets that allows you to perform calculations on an array of data, rather than a single data point.
Google Finance: A function unique to Google Sheets that fetches current or historical securities information from Google Finance.
IMPORTRANGE: A function in Google Sheets that allows you to import a range of cells from one spreadsheet to another.
QUERY: A function in Google Sheets that allows you to perform a query over an array of values. It's similar to SQL in database management.
SPLIT: A Google Sheets function that divides text around a specified character or string and puts each fragment into a separate cell in the row.
Advanced Features
More advanced spreadsheet features and concepts such as macros, data validation, array formulas, etc.
Array Formulas: A formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results, or a single result. For example, {=SUM(A1:A3*B1:B3)} multiplies each individual pair of values in the two arrays and then adds them all together.
Data Validation: A feature that you can use in Excel to define restrictions on what data can or should be entered in a cell. For example, you can restrict input to a certain range of numbers, dates, or even a list of predefined values.
Goal Seek: An Excel feature that adjusts the value in a specified cell until a formula that's dependent on that cell returns a desired value. It's a type of what-if analysis.
Macros: A sequence of commands or instructions that perform a specified task. In Excel, macros are created in Visual Basic for Applications (VBA).
Named Range: A descriptive name for a collection of cells or range in a worksheet. Named ranges are particularly useful when used in formulas, as they make the formula much easier to understand and maintain.
Pivot Tables: A data summarization tool in Excel. It can sort, count, total, or average the data stored in one table or spreadsheet and create a second table displaying the summarized data.
Solver: A tool in Excel that performs what-if analysis. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.
VBA (Visual Basic for Applications): The programming language used by Office applications to automate tasks. For example, you might create a VBA script to format a report, connect to a database, or update data automatically.
What-if Analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, with data tables in Excel, you can see the outcomes in many different cells for various scenarios in a single table.
Workbook and Worksheet Protection: Excel provides several levels of security that prevent unauthorized access to data. These include protection for individual cells, worksheets, and entire workbooks.