10 Excel Analytics courses in London

Excel but different. Harness this most powerful tool but on a course tailored to your business and needs. Using case studies relevant to your business and often actual files your firm uses on a daily basis we embark on a voyage of discovery. This isn't a boring "watch what I do and copy" course. This is exciting, engaging, funny and enjoyable. Delegates will learn relevant skills but also, because of the delivery methods they will gain an increased enjoyment of using Excel and will retain and implement all of the material covered. They also have access to the trainer for an unlimited amount of time after the course to continue to grow their knowledge

Excel but different. Harness this most powerful tool but on a course tailored to your business and needs. Using case studies relevant to your business and often actual files your firm uses on a daily basis we embark on a voyage of discovery. This isn't a boring "watch what I do and copy" course. This is exciting, engaging, funny and enjoyable. Delegates will learn relevant skills but also, because of the delivery methods they will gain an increased enjoyment of using Excel and will retain and implement all of the material covered. They also have access to the trainer for an unlimited amount of time after the course to continue to grow their knowledge

This one-day course focuses on issues such as writing formulas and accessing help while writing them, and taking formulas to the next level by nesting one inside another for a powerful formula result. It also looks at ways of analysing data with reports, summarised by varying criteria. A range of time-saving tips and tricks are shared. This course will help participants: * Calculate with absolute reference * Group worksheets * Link to tables * Use the function library effectively * Get to grips with the logical IF function * Use conditional formatting * Create pivot table reports * Use data validation * Master the VLOOKUP function 1 CALCULATING WITH ABSOLUTE REFERENCE * The difference between a relative and absolute formula * Changing a relative formula to an absolute * Using $ signs to lock cells when copying formulas 2 GROUPING WORKSHEETS * Grouping sheets together * Inputting data into multiple sheets * Writing a 3D formula to sum tables across sheets 3 LINKING TO TABLES * Linking to a source table * Using paste link to link a table to another file * Using edit links to manage linked tables 4 THE FUNCTION LIBRARY * Benefits of writing formulas in the function library * Finding the right formula using insert function * Outputting statistics with COUNTA and COUNTBLANK * Counting criteria in a list with COUNTIFS 5 LOGICAL IF FUNCTION * Outputting results from tests * Running multiple tests for multiple results * The concept of outputting results from numbers 6 CONDITIONAL FORMATTING * Enabling text and numbers to standout * Applying colour to data using rules * Managing rules * Copying rules with the format painter 7 VIEW SIDE BY SIDE * Comparing two Excel tables together * Comparing two sheets together in the same file 8 PIVOT TABLE REPORTS * Analysing data with pivot tables * Managing a pivot table's layout * Outputting statistical reports * Controlling number formats * Visualising reports with pivot charts * Inserting slicers for filtering data 9 DATA VALIDATION * Restricting data input with data validation * Speeding up data entry with data validation 10 VLOOKUP FUNCTION * Best practices for writing a VLOOKUP * A false type lookup * A true type lookup * Enhance formula results with IFNA 11 PRINT OPTIONS * Getting the most from print * Printing page titles across pages * Scaling content for print

This one-day workshop is designed to give you an awareness of the fundamentals of Microsoft Excel and, in particular, to give you the confidence needed to efficiently create, edit and manage spreadsheets. This course will help participants: * Create tables * Use functions * Manage rows and columns * Write formulas * Manage sheets * Use content formats * Handle larger tables * Create reports and charts 1 CREATING A TABLE * Creating an Excel table from scratch * Wrapping text in cells * Speeding up data entry using AutoFill * Sorting columns 2 INSERTING FUNCTION * Inserting function calculators * Using AutoSum to sum numbers * Statistical calculations AVERAGE, MAX and MIN 3 TABLE ROWS AND COLUMNS * Inserting and deleting rows and columns * Adjusting multiple column widths and row heights * Hiding and unhiding rows and columns 4 FORMULA WRITING * The basics of formula writing * Understanding mathematical symbols * Using multiple mathematical symbols in a formula * When to use brackets * Troubleshooting calculation errors 5 MANAGING SHEETS * Inserting, renaming, moving and deleting sheets * Copying a worksheet to another file * Copying a table to another sheet 6 MANAGING CONTENT FORMATS * Applying data formats * Managing number formats * Controlling formats with the Format Painter 7 MANAGING LARGER TABLES * Applying freeze panes to lock tables when scrolling * Sorting on multiple columns * Using filters to extract table information 8 CREATING AND MODIFYING CHARTS * Creating a pie chart * Creating a column chart * Inserting chart titles and data labels * Controlling chart formatting * Changing chart types 9 PRINTING * Previewing and printing tables and charts * Modifying page orientation * Adjusting print margins * Printing a selection 10 CALCULATING WITH ABSOLUTE REFERENCE * The difference between a relative and absolute formula * Changing a relative formula to an absolute * Using $ signs to lock cells when copying formulas 11 PIVOT TABLES * Create a pivot table report * Insert a pivot chart into a report

Most people only use a fraction of Excel's capabilities. This workshop shows what you've been missing! This course will help participants: * Nest formulas * Get the most from pivot tables * Use conditional formatting * Write array formulas * Explore the lookup functions * Calculate by criteria * Use 'goal seek' and 'scenario manager' for what-if analysis * Record macros 1 NESTING FORMULAS * Principles of nesting formulas together * Using IF with AND or OR to answer questions * Nesting an AND function in an IF * Nesting an OR function in an IF 2 ADVANCED PIVOT TABLE REPORTS * Grouping dates, numerical and text items * Running percentage analyse * Running analyses to compare data * Inserting Field calculations * Finishing off with a user-friendly dashboard 3 ADVANCED CONDITIONAL FORMATTING * Colour table rows based on criteria in it * Applying colour to approaching dates * Exploring the different rule types 4 LOOKUP FUNCTIONS * Going beyond the VLOOKUP function * Lookups that retrieve data from left or right * The versatile INDEX and MATCH functions * Retrieving data from columns with duplicates 5 CALCULATE BY CRITERIA * Using SUMIFS to sum by criteria * Finding an average by criteria with AVERAGEIFS * Use SUMPRODUCT to multiply then add different values 6 WHAT-IF ANALYSIS * Use Goal Seek to meet targets * Forecast reports with the Scenario Manager 7 RECORDING MACROS * Macro security * Understanding a Relative References macro * Recording, running and editing macros * Saving files as Macro Enabled Workbooks * Introduction to VBA code * Making macros available across workbooks * Add a macro button to the Quick Access toolbar

This one-day workshop will give you a better understanding of the components and operations of an Access database. It is designed to build on a user's existing skills and includes useful action queries to allow greater manipulation of a database. This workshop will help participants: * Ensure the integrity of their databases * Manage field properties * Use the query functions effectively * Save time with the query expression builder * Create different types of query more quickly * Design better forms * Link expressions in forms * Create better and more useful reports * Import and export tables more easily 1 TABLE RELATIONSHIP INTEGRITY * Identifying relationships * Identifying criteria for data integrity * Applying referential integrity * Managing relationship join types 2 TABLE FIELD PROPERTIES * Field properties overview * Using input mask field * Using default value fields * Using field validation rules 3 QUERY FUNCTIONS * Running aggregate function calculations * Running sum, average, count, max and min functions * Grouping calculated data 4 QUERY CALCULATIONS * Using query operators and expressions * Adding calculated fields to a query * Using the query expression builder 5 ACTION QUERIES * Creating make table queries * Creating append queries * Creating update queries * Creating delete queries 6 DESIGNING FORMS * Adding form controls * Aligning and arranging form controls * Adding pictures and labels to forms * Adding new fields to a form * Controlling tab order * Adding command buttons * Adding a combo box control * Formatting data using conditional formatting 7 FORM EXPRESSIONS (CALCULATIONS) * Using the form expression builder * Working with a property sheet within a form * Linking expressions within a form 8 WORKING WITH REPORTS * Creating reports with the report wizard * Inserting report fields * Formatting fields * Inserting report headers and footers * Working with a property sheet within a report 9 GROUPED REPORTS * Creating groups with the report wizard * Sorting grouped data * Grouping alphabetically * Grouping on date intervals * Creating sub reports * Adding calculations to groups 10 IMPORTING AND EXPORTING TABLES * Importing tables into Access * Exporting tables from Access * Importing and linking data in Access

This course starts with the basics then moves seamlessly to an intermediate level. It includes a comprehensive yet balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Model view, and the Power Query Editor. It also demonstrates how to use the online Power BI service. It looks at authoring tools that enables you to connect to and transform data from a variety of sources, allowing you to produce dynamic reports using a library of visualisations. Once you have those reports, the course looks at the seamless process of sharing those with your colleagues by publishing to the online Power BI service. The aim of this course is to provide a strong understanding of the Power BI analysis process, by working with real-world examples that will equip you with the necessary skills to start applying your knowledge straight away. 1 GETTING STARTED * The Power BI process * Launching Power BI Desktop * The four views of Power BI * Dashboard visuals 2 CONNECTING TO FILES * Connect to data sources * Connect to an Excel file * Connect to a CSV file * Connect to a database * Import vs. DirectQuery * Connect to a web source * Create a data table 3 TRANSFORMING DATA * The process of cleaning data * Column data types * Remove rows with filters * Add a custom column * Append data to a table * Fix error issues * Basic maths operations 4 BUILD A DATA MODEL * Table relationships * Manage table relationships 5 MERGE QUERIES * Table join kinds * Merging tables 6 CREATE REPORT VISUALISATIONS * Creating map visuals * Formatting maps * Creating chart visuals * Formatting chart * Tables, matrixes, and cards * Control formatting with themes * Filter reports with slicers * Reports for mobile devices * Custom online visuals * Export report data to Excel 7 THE POWER QUERY EDITOR * Fill data up and down * Split columns by delimiter * Add conditional columns * Merging columns 8 THE M FORMULA * Creating M functions * Create an IF function * Create a query group 9 PIVOT AND UNPIVOT TABLES * Pivot tables in the query editor * Pivot and append tables * Pivot but don't summarise * Unpivot tables * Append mismatched headers 10 DATA MODELLING REVISITED * Data model relationships * Mark a calendar as a date table 11 INTRODUCTION TO CALCULATED COLUMNS * New columns vs. measures * Creating a new column calculation * The SWITCH function 12 INTRODUCTION TO DAX MEASURES * Common measure categories * The SUM measure * Adding measures to visuals * COUNTROWS and DISINCTCOUNT functions * DAX rules 13 THE CALCULATE MEASURE * The syntax of CALCULATE * Things of note about CALCULATE 14 THE SUMX MEASURE * The SUMX measure * X iterator functions * Anatomy of SUMX 15 INTRODUCTION TO TIME INTELLIGENCE * Importance of a calendar table * A special lookup table * The TOTALYTD measure * Change year end in TOTALYTD 16 HIERARCHY, GROUPS AND FORMATTING * Create a hierarchy to drill data * Compare data in groups * Add conditional formatting 17 SHARE REPORTS ON THE WEB * Publish to the BI online service * Get quick insights * Upload reports from BI service * Exporting report data * What is Q&A? * Sharing your reports 18 APPLY YOUR LEARNING * Post training recap lesson

There is a lot to learn in Power BI, this course takes a comprehensive look at the fundamentals of analysing data and includes a balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Model view, and the Power Query Editor. It also demonstrates how to utilise the online Power BI service. It looks at authoring tools that enable you to connect to and transform data from a variety of sources, allowing you to produce detailed reports through a range of visualisations, in an interactive and dynamic way. It also includes a detailed look at formulas by writing both M functions in Power Query, and DAX functions in Desktop view. This knowledge will allow you to take your reports to the next level. The aim of this course is to provide a complete introduction to understanding the Power BI analysis process, by working hands-on with examples that will equip you with the necessary skills to start applying your learning straight away. 1 GETTING STARTED * The Power BI ecosystem * Opening Power BI Desktop * Power BI's four views * Introduction to Dashboards 2 IMPORTING FILES * Importing data sources * Importing an Excel file * Importing a CSV file * Importing a database * Connect to an SQL Server Database * Import vs. Direct Query * Importing from the web * Importing a folder of files * Managing file connections 3 SHAPE DATA IN THE QUERY EDITOR * The process of shaping data * Managing data types * Keeping and removing rows * Add a custom column * Appending tables together * Hiding queries in reports * Fixing error issues * Basic maths operations 4 THE DATA MODEL * Table relationships * Relationship properties 5 MERGE QUERIES * Table join kinds * Merging tables 6 INSERTING DASHBOARD VISUALS * Things to keep in mind * Inserting maps * Formatting Maps * Inserting charts * Formatting Charts * Inserting a tree map * Inserting a table, matrix, and card * Controlling number formats * About report themes * Highlighting key points * Filter reports with slicers * Sync slicers across dashboards * Custom web visuals 7 PUBLISH AND SHARE REPORTS * Publishing to Power BI service * Editing online reports * Pinning visuals to a dashboard * What is Q&A? * Sharing dashboards * Exporting reports to PowerPoint * Exporting reports as PDF files 8 THE POWER QUERY EDITOR * Fill data up and down * Split column by delimiter * Add a conditional column * More custom columns * Merging columns 9 THE M FUNCTIONS * Inserting text functions * Insert an IF function * Create a query group 10 PIVOTING TABLES * Pivot a table * Pivot and append tables * Pivot but don't aggregate * Unpivot tables * Append mismatched headers 11 DATA MODELLING EXPANDED * Understanding relationships * Mark a date table 12 DAX NEW COLUMNS * New columns and measures * New column calculations * Insert a SWITCH function 13 INTRODUCTION TO DAX MEASURES * Common measure functions * Insert a SUM function * Insert a COUNTROWS function * Insert a DISTINCTCOUNT function * Insert a DIVIDE function * DAX rules 14 THE CALCULATE MEASURE * The syntax of CALCULATE * Insert a CALCULATE function * Control field summarisation * Things of note 15 THE SUMX MEASURE * X iterator functions * Anatomy of SUMX * Insert a SUMX function * When to use X functions 16 TIME INTELLIGENCE MEASURES * Importance of a calendar table * Insert a TOTALYTD function * Change financial year end date * Comparing historical data * Insert a DATEADD function 17 HIERARCHIES AND GROUPS * Mine data using hierarchies * Compare data in groups

This course is designed for those already using Power BI Desktop and are ready to work with more comprehensive elements of analysing and reporting in Power BI. The course maintains a balanced look at data analysis including the Power Query Editor, with a deep dive into writing DAX formulas, and enhanced dashboard visualisations. The aim of this course is to provide a more complete understanding of the whole Power BI analytics process, by working with business examples that will equip you with the necessary skills to output comprehensive reports and explore Power BI's analytical capabilities in more depth. 1 THE QUERY EDITOR * Grouping rows in a table * Split row by delimiter * Add days to determine deadlines * The query editor 2 FUZZY MATCHING JOINS * Matching inconsistencies by percentage * Matching with transformation table 3 THE QUERY EDITOR M FUNCTIONS * Adding custom columns * Creating an IF function * Nested AND logics in an IF function 4 DAX NEW COLUMNS FUNCTIONS * Including TRUE with SWITCH * Using multiple conditions * The FIND DAX function * The IF DAX function * Logical functions IF, AND, OR 5 EDITING DAX MEASURES * Making DAX easier to read * Add comments to a measure * Using quick measures 6 THE ANATOMY OF CALCULATE * Understanding CALCULATE filters * Add context to CALCULATE with FILTER * Using CALCULATE with a threshold 7 THE ALL MEASURE * Anatomy of ALL * Create an ALL measure * Using ALL as a filter * Use ALL for percentages 8 DAX ITERATORS * Anatomy of iterators * A closer look at SUMX * Using RELATED with SUMX * Create a RANKX * RANKX with ALL 9 DATE AND TIME FUNCTIONS * Overview of functions * Create a DATEDIFF function 10 TIME INTELLIGENT MEASURES * Compare historical monthly data * Create a DATEADD measure * Creating cumulative totals * Creating cumulative measures * Visualising cumulative totals 11 VISUALISATIONS IN-DEPTH * Utilising report themes * Applying static filters * Group data using lists * Group numbers using bins * Creating heatmaps * Comparing proportions * View trends with sparklines 12 COMPARING VARIABLES * Visualising trendlines as KPI * Forecasting with trendlines * Creating a scatter plot * Creating dynamic labels * Customised visualisation tooltips * Export reports to SharePoint

This course starts with data transformation strategies, exploring capabilities in the Power Query Editor, and data-cleansing practices. It looks at the Advanced Query Editor to view the M language code. This course focuses on advanced DAX measures that include filtering conditions, with a deep dive into time intelligence measures. Like the M query language, DAX is a rich functional language that supports variables and expression references. This course also looks at the creation of dynamic dashboards and incorporates a range of visualisations available in Power BI Desktop and online in the AppSource. The course finishes with a look at setting up end user level security in tables. 1 THE QUERY EDITOR * Split by row delimiter * AddDays to determine deadlines * Advanced query editor 2 FUZZY MATCHING JOINS * Matching inconsistencies by percentage * Matching with transformation table 3 LOGICAL COLUMN FUNCTIONS * Logical functions IF, AND, OR * Using multiple conditions * Including FIND in functions 4 EDITING DAX MEASURES * Make DAX easier to read * Add comments to a measure * Using quick measures 5 THE ANATOMY OF CALCULATE * Understanding CALCULATE context filters * Adding context to CALCULATE with FILTER * Using CALCULATE with a threshold 6 THE ALL MEASURE * Anatomy of ALL * Create an ALL measure * Using ALL as a filter * Use ALL for percentage 7 DAX ITERATORS * Anatomy of iterators * A closer look at SUMX * Using RELATED in SUMX * Create a RANKX * RANKX with ALL 8 DATE AND TIME FUNCTIONS * Overview of functions * Create a DATEDIFF function 9 TIME INTELLIGENT MEASURES * Compare historical monthly data * Create a DATEADD measure * Creating cumulative totals * Creating cumulative measures * Visualising cumulative totals 10 VISUALISATIONS IN-DEPTH * Utilising report themes * Create a heatmap * Comparing proportions * View trends with sparklines * Group numbers using bins * Setting up a histogram 11 COMPARING VARIABLES * Visualising trendlines as KPI * Forecasting with trendlines * Creating a scatter plot * Creating dynamic labels * Customised visualisation tooltips * Export reports to SharePoint 12 USER LEVEL SECURITY * Setting up row level security * Testing user security