Advanced Pivot Tables for Accounting
Expert-defined terms from the Professional Certificate in Excel for Accounting Professionals (United Kingdom) course at London School of Business and Administration. Free to read, free to share, paired with a professional course.
Absolute Reference refers to a fixed cell reference in a formula that rem… #
Related terms include Relative Reference and Mixed Reference. In the context of Advanced Pivot Tables for Accounting, Absolute Reference is used to ensure that the formula refers to the same cell or range of cells, even when the Pivot Table is updated or refreshed. For example, if you want to reference the cell A1 in a formula, you would use $A$1 to ensure that the reference remains fixed.
Accounting Equation is a fundamental concept in accounting that states th… #
Related terms include Assets, Liabilities, and Equity. In the context of Advanced Pivot Tables for Accounting, the Accounting Equation is used to analyze and report financial data, such as creating a Pivot Table to show the balance of Assets, Liabilities, and Equity over time. For example, you can create a Pivot Table to show the total Assets, Liabilities, and Equity for a company, and then use the Accounting Equation to calculate the net worth of the company.
Active Cell is the current cell that is selected in a worksheet, and it i… #
Related terms include Cell Reference and Range. In the context of Advanced Pivot Tables for Accounting, the Active Cell is used to enter data, formulas, and formatting, and it is also used to select cells for analysis and reporting. For example, if you want to enter a formula in a cell, you would first select the cell to make it the Active Cell, and then enter the formula.
Add #
in is a software program that adds new features and functionality to Excel, such as the Analysis ToolPak or Power Pivot. Related terms include Plug-in and Macro. In the context of Advanced Pivot Tables for Accounting, Add-ins are used to extend the capabilities of Excel, such as creating complex financial models or analyzing large datasets. For example, you can use the Power Pivot Add-in to create a data model that combines data from multiple sources, and then use the data model to create a Pivot Table.
Analysis ToolPak is an add #
in program that provides advanced statistical and analytical tools, such as regression analysis and hypothesis testing. Related terms include Data Analysis and Statistical Analysis. In the context of Advanced Pivot Tables for Accounting, the Analysis ToolPak is used to analyze and interpret financial data, such as creating a Pivot Table to show the trend of sales over time, and then using the Analysis ToolPak to forecast future sales. For example, you can use the regression analysis tool to analyze the relationship between sales and marketing expenses, and then use the results to create a budget.
Asset is a resource that is owned or controlled by a company, such as cas… #
Related terms include Liability and Equity. In the context of Advanced Pivot Tables for Accounting, Assets are used to prepare financial statements, such as the Balance Sheet, and to analyze and report financial data. For example, you can create a Pivot Table to show the total Assets of a company, and then use the data to calculate the return on assets.
AutoFilter is a feature that allows you to filter data in a worksheet bas… #
Related terms include Filter and Sort. In the context of Advanced Pivot Tables for Accounting, AutoFilter is used to select and analyze specific data, such as filtering a Pivot Table to show only the sales data for a specific region. For example, you can use AutoFilter to select only the data for a specific customer, and then use the selected data to create a Pivot Table.
Axis is a dimension of a chart or graph, such as the x #
axis or y-axis. Related terms include Chart and Graph. In the context of Advanced Pivot Tables for Accounting, Axis is used to create visualizations of financial data, such as creating a chart to show the trend of sales over time. For example, you can use the x-axis to show the months of the year, and the y-axis to show the sales amounts.
Balance Sheet is a financial statement that shows the financial position… #
Related terms include Income Statement and Cash Flow Statement. In the context of Advanced Pivot Tables for Accounting, the Balance Sheet is used to analyze and report financial data, such as creating a Pivot Table to show the balance of Assets, Liabilities, and Equity over time. For example, you can create a Pivot Table to show the total Assets, Liabilities, and Equity for a company, and then use the data to calculate the net worth of the company.
Calculated Field is a field in a Pivot Table that is calculated using a f… #
Related terms include Formula and Field. In the context of Advanced Pivot Tables for Accounting, Calculated Fields are used to create custom calculations and analysis, such as creating a Pivot Table to show the total sales for a region, and then using a Calculated Field to calculate the sales per customer. For example, you can use a Calculated Field to calculate the average sales per customer, and then use the results to create a budget.
Cell Reference is a reference to a cell or range of cells in a worksheet,… #
Related terms include Absolute Reference and Relative Reference. In the context of Advanced Pivot Tables for Accounting, Cell References are used to enter data, formulas, and formatting, and to select cells for analysis and reporting. For example, if you want to enter a formula in a cell, you would first select the cell to make it the Active Cell, and then enter the formula using a Cell Reference.
Chart is a visual representation of data, such as a bar chart or line cha… #
Related terms include Graph and Axis. In the context of Advanced Pivot Tables for Accounting, Charts are used to create visualizations of financial data, such as creating a chart to show the trend of sales over time. For example, you can use a chart to show the sales data for a specific region, and then use the chart to analyze and interpret the data.
Consolidation is the process of combining data from multiple sources, suc… #
Related terms include Data Combination and Data Integration. In the context of Advanced Pivot Tables for Accounting, Consolidation is used to create a unified view of financial data, such as consolidating data from multiple worksheets to create a single Pivot Table. For example, you can use Consolidation to combine data from multiple regions,! and then use the consolidated data to create a Pivot Table.
Data Analysis is the process of examining and interpreting data to extrac… #
Related terms include Statistical Analysis and Data Interpretation. In the context of Advanced Pivot Tables for Accounting, Data Analysis is used to analyze and interpret financial data, such as creating a Pivot Table to show the trend of sales over time, and then using statistical methods to forecast future sales. For example, you can use Data Analysis to analyze the relationship between sales and marketing expenses, and then use the results to create a budget.
Data Combination is the process of combining data from multiple sources,… #
Related terms include Consolidation and Data Integration. In the context of Advanced Pivot Tables for Accounting, Data Combination is used to create a unified view of financial data, such as combining data from multiple worksheets to create a single Pivot Table. For example, you can use Data Combination to combine data from multiple regions, and then use the combined data to create a Pivot Table.
Data Interpretation is the process of assigning meaning to data, such as… #
Related terms include Data Analysis and Statistical Analysis. In the context of Advanced Pivot Tables for Accounting, Data Interpretation is used to analyze and interpret financial data, such as creating a Pivot Table to show the trend of sales over time, and then using statistical methods to forecast future sales. For example, you can use Data Interpretation to analyze the relationship between sales and marketing expenses, and then use the results to create a budget.
Data Model is a representation of data that is used to create a unified v… #
Related terms include Data Combination and Data Integration. In the context of Advanced Pivot Tables for Accounting, Data Models are used to create a unified view of financial data, such as creating a data model that combines data from multiple worksheets to create a single Pivot Table. For example, you can use a Data Model to combine data from multiple regions, and then use the data model to create a Pivot Table.
Data Validation is a feature that allows you to restrict the type of data… #
Related terms include Data Entry and Data Quality. In the context of Advanced Pivot Tables for Accounting, Data Validation is used to ensure that financial data is accurate and consistent, such as using Data Validation to restrict the data entry to a specific format or range. For example, you can use Data Validation to restrict the data entry to a specific date format, and then use the validated data to create a Pivot Table.
Depreciation is a non #
cash expense that represents the decrease in value of an asset over time, such as the depreciation of a building or equipment. Related terms include Amortization and Impairment. In the context of Advanced Pivot Tables for Accounting, Depreciation is used to calculate the expense of depreciation, such as creating a Pivot Table to show the depreciation expense over time. For example, you can use a Pivot Table to show the depreciation expense for a specific asset, and then use the data to calculate the net book value of the asset.
Detail Row is a row in a Pivot Table that shows the detailed data for a s… #
Related terms include Data Field and Field. In the context of Advanced Pivot Tables for Accounting, Detail Rows are used to show the detailed data for a specific field, such as creating a Pivot Table to show the sales data for a specific region, and then using a Detail Row to show the detailed data for the region. For example, you can use a Detail Row to show the sales data for a specific customer, and then use the data to create a budget.
Field is a column or row in a Pivot Table that contains a specific… #
Related terms include Data Field and Detail Row. In the context of Advanced Pivot Tables for Accounting, Fields are used to create a Pivot Table, such as creating a Pivot Table to show the sales data for a specific region, and then using a Field to show the sales data for the region. For example, you can use a Field to show the sales data for a specific customer, and then use the data to create a budget.
Field List is a pane in the Pivot Table interface that shows the availabl… #
Related terms include Field and Data Field. In the context of Advanced Pivot Tables for Accounting, the Field List is used to select the fields that are used to create a Pivot Table, such as selecting the fields that contain sales data or customer data. For example, you can use the Field List to select the fields that contain sales data for a specific region, and then use the selected fields to create a Pivot Table.
Filter is a feature that allows you to select specific data based on crit… #
Related terms include AutoFilter and Sort. In the context of Advanced Pivot Tables for Accounting, Filters are used to select specific data, such as filtering a Pivot Table to show only the sales data for a specific region. For example, you can use a Filter to select only the data for a specific customer, and then use the selected data to create a Pivot Table.
Financial Statement is a report that shows the financial position and per… #
Related terms include Balance Sheet and Income Statement. In the context of Advanced Pivot Tables for Accounting, Financial Statements are used to analyze and report financial data, such as creating a Pivot Table to show the balance of Assets, Liabilities, and Equity over time. For example, you can create a Pivot Table to show the total Assets, Liabilities, and Equity for a company, and then use the data to calculate the net worth of the company.
Formula is a mathematical expression that is used to calculate a value, s… #
Related terms include Function and Calculation. In the context of Advanced Pivot Tables for Accounting, Formulas are used to create custom calculations and analysis, such as creating a Pivot Table to show the total sales for a region, and then using a Formula to calculate the sales per customer. For example, you can use a Formula to calculate the average sales per customer, and then use the results to create a budget.
Function is a pre #
defined formula that is used to perform a specific calculation, such as a function that calculates the sum of a range of cells. Related terms include Formula and Calculation. In the context of Advanced Pivot Tables for Accounting, Functions are used to create custom calculations and analysis, such as creating a Pivot Table to show the total sales for a region, and then using a Function to calculate the sales per customer. For example, you can use a Function to calculate the average sales per customer, and then use the results to create a budget.
Graph is a visual representation of data, such as a bar graph or line gra… #
Related terms include Chart and Axis. In the context of Advanced Pivot Tables for Accounting, Graphs are used to create visualizations of financial data, such as creating a graph to show the trend of sales over time. For example, you can use a graph to show the sales data for a specific region, and then use the graph to analyze and interpret the data.
Hierarchy is a structure that shows the relationships between different f… #
Related terms include Field and Data Field. In the context of Advanced Pivot Tables for Accounting, Hierarchies are used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales data for a specific region, and then using a Hierarchy to show the relationships between the sales data and customer data. For example, you can use a Hierarchy to show the relationships between sales data and customer data, and then use the hierarchy to create a budget.
Income Statement is a financial statement that shows the revenues and exp… #
Related terms include Balance Sheet and Financial Statement. In the context of Advanced Pivot Tables for Accounting, the Income Statement is used to analyze and report financial data, such as creating a Pivot Table to show the revenues and expenses over time. For example, you can create a Pivot Table to show the total revenues and expenses for a company, and then use the data to calculate the net income of the company.
KPI is a key performance indicator that is used to measure the performanc… #
Related terms include Metric and Benchmark. In the context of Advanced Pivot Tables for Accounting, KPIs are used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales revenue over time, and then using a KPI to measure the performance of the company. For example, you can use a KPI to measure the sales revenue per customer, and then use the results to create a budget.
Liability is a debt or obligation that is owed by a company, such… #
Related terms include Asset and Equity. In the context of Advanced Pivot Tables for Accounting, Liabilities are used to prepare financial statements, such as the Balance Sheet, and to analyze and report financial data. For example, you can create a Pivot Table to show the total Liabilities of a company, and then use the data to calculate the debt-to-equity ratio.
Macro is a series of instructions that are used to automate a task, such… #
Related terms include Add-in and Automation. In the context of Advanced Pivot Tables for Accounting, Macros are used to automate tasks, such as creating a Pivot Table to show the sales data for a specific region, and then using a Macro to update the Pivot Table automatically. For example, you can use a Macro to create a Pivot Table to show the sales data for a specific customer, and then use the Macro to update the Pivot Table automatically.
Metric is a measure of performance that is used to evaluate the success o… #
Related terms include KPI and Benchmark. In the context of Advanced Pivot Tables for Accounting, Metrics are used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales revenue over time, and then using a Metric to measure the performance of the company. For example, you can use a Metric to measure the sales revenue per customer, and then use the results to create a budget.
Mixed Reference is a reference to a cell or range of cells in a worksheet… #
Related terms include Absolute Reference and Relative Reference. In the context of Advanced Pivot Tables for Accounting, Mixed References are used to create flexible and dynamic formulas, such as creating a formula that calculates the total sales for a region, and then using a Mixed Reference to refer to the sales data. For example, you can use a Mixed Reference to refer to the sales data for a specific customer, and then use the reference to create a budget.
OLAP is a technology that is used to analyze and report data, such as cre… #
Related terms include Data Analysis and Business Intelligence. In the context of Advanced Pivot Tables for Accounting, OLAP is used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales data for a specific region, and then using OLAP to analyze and interpret the data. For example, you can use OLAP to create a Pivot Table to show the sales data for a specific customer, and then use the Pivot Table to analyze and interpret the data.
Pivot Cache is a copy of the data that is used to create a Pivot Table, a… #
Related terms include Pivot Table and Data Source. In the context of Advanced Pivot Tables for Accounting, the Pivot Cache is used to improve performance and reduce the amount of data that is transferred, such as creating a Pivot Table to show the sales data for a specific region, and then using the Pivot Cache to store the data in memory. For example, you can use the Pivot Cache to store the sales data for a specific customer, and then use the Pivot Cache to create a Pivot Table.
Pivot Table is a summary of data that is used to analyze and report finan… #
Related terms include Data Source and Pivot Cache. In the context of Advanced Pivot Tables for Accounting, Pivot Tables are used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales data for a specific region, and then using the Pivot Table to analyze and interpret the data. For example, you can use a Pivot Table to show the sales data for a specific customer, and then use the Pivot Table to analyze and interpret the data.
Plug #
in is a software program that adds new features and functionality to Excel, such as a plug-in that creates a Pivot Table. Related terms include Add-in and Macro. In the context of Advanced Pivot Tables for Accounting, Plug-ins are used to extend the capabilities of Excel, such as creating a Pivot Table to show the sales data for a specific region, and then using a Plug-in to update the Pivot Table automatically. For example, you can use a Plug-in to create a Pivot Table to show the sales data for a specific customer, and then use the Plug-in to update the Pivot Table automatically.
Power Pivot is an add #
in program that provides advanced data analysis and reporting capabilities, such as creating a Pivot Table to show the sales data for a specific region. Related terms include Pivot Table and Data Model. In the context of Advanced Pivot Tables for Accounting, Power Pivot is used to create a framework for analyzing and reporting financial data, such as creating a Pivot Table to show the sales data for a specific region, and then using Power Pivot to analyze and interpret the data. For example, you can use Power Pivot to create a Pivot Table to show the sales data for a specific customer, and then use the Pivot Table to analyze and interpret the data.
Range is a group of cells in a worksheet that are selected for analysis o… #
Related terms include Cell Reference and Field. In the context of Advanced Pivot Tables for Accounting, Ranges are used to select specific data, such as selecting a range that contains sales data for a specific region, and then using the range to create a Pivot Table. For example, you can use a Range to select the sales data for a specific customer, and then use the range to create a budget.
Relative Reference is a reference to a cell or range of cells in a worksh… #
Related terms include Absolute Reference and Mixed Reference. In the context of Advanced Pivot Tables for Accounting, Relative References are used to create flexible and dynamic formulas, such as creating a formula that calculates the total sales for a region, and then using a Relative Reference to refer to the sales data. For example, you can use a Relative Reference to refer to the sales data for a specific customer, and then use the reference to create a budget.
Report is a document that shows the results of an analysis or investigati… #
Related terms include Pivot Table and Financial Statement. In the context of Advanced Pivot Tables for Accounting, Reports are used to communicate the results of an analysis or investigation, such as creating a report to show the sales data for a specific region, and then using the report to analyze and interpret the data. For example, you can use a report to show the sales data for a specific customer, and then use the report to create a budget.
Row Label is a label that is used to identify a row in a Pivot Table, suc… #
Related terms include Column Label and Field. In the context of Advanced Pivot Tables for Accounting, Row Labels are used to identify the rows in a Pivot Table, such as creating a Pivot Table to show the sales data for a specific region, and then using a Row Label to identify the rows. For example, you can use a Row Label to identify the rows that show the sales data for a specific customer, and then use the label to create a budget.
Slicer is a feature that allows you to filter data in a Pivot Table based… #
Related terms include Filter and Pivot Table. In the context of Advanced Pivot Tables for Accounting, Slicers are used to select specific data, such as selecting a slicer that filters the data to show only the sales data for a specific region, and then using the slicer to create a Pivot Table. For example, you can use a Slicer to select only the data for a specific customer, and then use the slicer to create a budget.
Sort is a feature that allows you to arrange data in a specific order, su… #
Related terms include Filter and AutoFilter. In the context of Advanced Pivot Tables for Accounting, Sort is used to arrange data in a specific order, such as sorting the data in a Pivot Table to show the sales data for a specific region in descending order. For example, you can use Sort to arrange the data in a Pivot Table to show the sales data for a specific customer in descending order, and then use the sorted data to create a budget.
SQL is a language that is used to manage and analyze data in a database,… #
Related terms include Database and Query. In the context of Advanced Pivot Tables for Accounting, SQL is used to manage and analyze data, such as creating a query to retrieve the sales data for a specific region, and then using the query to create a Pivot Table. For example, you can use SQL to create a query to retrieve the sales data for a specific customer, and then use the query to create a budget.
Statistical Analysis is the process of examining and interpreting data us… #
Related terms include Data Analysis and Data Interpretation. In the context of Advanced Pivot Tables for Accounting, Statistical Analysis is used to analyze and interpret financial data, such as creating a Pivot Table to show the trend of sales over time, and then using statistical methods to forecast future sales. For example, you can use Statistical Analysis to analyze the relationship between sales and marketing expenses, and then use the results to create a budget.
Table is a structure that is used to organize and display data, such as a… #
Related terms include Pivot Table and Data Source. In the context of Advanced Pivot Tables for Accounting, Tables are used to organize and display financial data, such as creating a table to show the sales data for a specific region, and then using the table to analyze and interpret the data. For example, you can use a table to show the sales data for a specific customer, and then use the table to create a budget.
Timeline is a feature that allows you to filter data in a Pivot Table bas… #
Related terms include Filter and Slicer. In the context of Advanced Pivot Tables for Accounting, Timelines are used to select specific data, such as selecting a timeline that filters the data to show only the sales data for a specific quarter, and then using the timeline to create a Pivot Table. For example, you can use a Timeline to select only the data for a specific year, and then use the timeline to create a budget.
Value Field is a field in a Pivot Table that contains a specific type of… #
Related terms include Data Field and Field. In the context of Advanced Pivot Tables for Accounting, Value Fields are used to create a Pivot Table, such as creating a Pivot Table to show the sales data for a specific region, and then using a Value Field to show the sales data for the region. For example, you can use a Value Field to show the sales data for a specific customer, and then use the data to create a budget.
View is a way of displaying data in a Pivot Table, such as a view that sh… #
Related terms include Pivot Table and Data Source. In the context of Advanced Pivot Tables for Accounting, Views are used to display financial data, such as creating a view to show the sales data for a specific region in a table format, and then using the view to analyze and interpret the data. For example, you can use a view to show the sales data for a specific customer in a chart format, and then use the view to create a budget.
VLOOKUP is a function that is used to look up data in a table, such as lo… #
Related terms include Function and Formula. In the context of Advanced Pivot Tables for Accounting, VLOOKUP is used to look up data, such as looking up the sales data for a specific customer, and then using the data to create a Pivot Table. For example, you can use VLOOKUP to look up the sales data for a specific region, and then use the data to create a budget.
Workbook is a file that contains one or more worksheets, such as a workbo… #
Related terms include Worksheet and Excel. In the context of Advanced Pivot Tables for Accounting, Workbooks are used to store and manage financial data, such as creating a workbook to store the sales data for a specific region, and then using the workbook to create a Pivot Table. For example, you can use a workbook to store the sales data for a specific customer, and then use the workbook to create a budget.
Worksheet is a page in a workbook that contains data, such as a worksheet… #
Related terms include Workbook and Excel. In the context of Advanced Pivot Tables for Accounting, Worksheets are used to store and manage financial data, such as creating a worksheet to store the sales data for a specific region, and then using the worksheet to create a Pivot Table. For example, you can use a worksheet to store the sales data for a specific customer, and then use the worksheet to create a budget.
XML is a language that is used to store and manage data, such as storing… #
Related terms include Data Storage and Data Management. In the context of Advanced Pivot Tables for Accounting, XML is used to store and manage financial data, such as storing the sales data for a specific region in an XML file, and then using the data to create a Pivot Table. For example, you can use XML to store the sales data for a specific customer, and then use the data to create a budget.