August 3, 2018
In Excel concatenation joins data from multiple cells together to display in a single cell. Whilst good table design requires data to be recorded in its smallest meaningful components, it is often more user friendly to display data components joined in spreadsheets and Pivot Tables etc.
August 3, 2018
In Power Pivot you can create calculated columns to add new data to tables. Calculated columns use a Data Analysis Expression (DAX) formula and can be applied in PivotTables, PivotCharts and reports.
August 3, 2018
PivotTables can summarize, group, filter, sort and chart large volumes of tabular data and can be re-configured with minimal effort, saving significant time.
August 3, 2018
A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook.
August 3, 2018
Once you create or import a table for each subject in your Data Model, you must enable the bringing together of related information in Excel again when needed. You do this by ensuring you have common fields in tables that are related, and by defining relationships between your tables. This […]
August 3, 2018
Excel tables either within the same workbook or in external workbooks can easily be added to a data model. Internal and external tables are, however, added using different processes.
August 3, 2018
Good table design is essential for the proper operation of Excel’s data management capabilities including sort, filter, consolidation, grouping, validation, PivotTable and Power Pivot .
August 3, 2018
It is important to understand how the Power Pivot Data Model connects to tables in an MS Access database.
August 3, 2018
Knowledge of database table design and relationship principles can greatly assist when managing and working with Power Pivot data models.
August 3, 2018
Adding the Go To The Power Pivot window command to the Quick Access Toolbar (QAT) will save time in navigating between the Power Pivot window and your Excel workbook.
August 2, 2018
Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated Data Models. With Power Pivot, you can manage large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
August 2, 2018
The application of lookup functions can be enhanced by combination with other Excel functions.
August 2, 2018
The versaltility of VLOOKUP can be significantly enhanced when you reference and link data from external worksheets and workbooks.
August 2, 2018
Lookup functions can rapidly and easily perform a multitude of tedious data matching tasks e.g. highlighting data mis-matches and anomalies between large tables; matching salaries to employees; awarding bonuses according to sales volumes, and mapping values from different sources into consolidated reports.
August 2, 2018
Lookup functions can rapidly and easily perform a multitude of tedious data matching tasks e.g. highlighting data mis-matches and anomalies between large tables; matching salaries to employees; awarding bonuses according to sales volumes, and mapping values from different sources into consolidated reports.
August 2, 2018
TRIM can remove all spaces from cells containing text except for single spaces between words.
August 2, 2018
NOTE: This course continues on from the course: Mastering Power Pivot Essentials in Excel L1 Power Pivot is a data modelling technology that allows you to manage large data sets, build extensive relationships, and create simple or complex calculations within the Excel application environment.
August 2, 2018
The Insert Function dialog box displays a clear description for each function you select and saves time by locating the exact function you require and guiding you through the syntax that applies to. It also contains a link to the help topic for each function you select.
August 2, 2018
Lookup functions can rapidly and easily perform a multitude of tedious data matching tasks e.g. highlighting data mis-matches and anomalies between large tables; matching salaries to employees; awarding bonuses according to sales volumes, and mapping values from different sources into consolidated reports.
August 2, 2018
Formula auditing can save significant time when checking formulas for accuracy or finding the source of an error within a worksheet and also between external worksheets and workbooks.