McTimothy Associates

This event has passed

INTERMEDIATE EXCEL FOR FINANCIAL AND BUSINESS ANALYSIS

Why Attend

The fact is that Excel is the accountant’s, finance, and business professional’s best friend. Companies everywhere are overwhelmed by the abundance of unstructured and unclean data. Monthly, quarterly and annual closings are immensely data-driven and require moving and exporting data from ‘ERPs’ and databases to Excel. In this hands-on course, McTimothy Associates will help you advance your data massaging, modeling, integration, and automation skills to new levels. You will also master normalization and massaging of noisy data, preparation of reports, analysis, and reconciliation.

This intermediate course is to make participants understand some critical functions and their usage in MS Excel. It starts with Data Manipulations, and goes on to Formatting and Displaying Techniques, Creating organizations, Linking Data and Worksheets, all through to Querying your Databases.

$ 106.14

Event Date: 24/08/2022 – 26/08/2022

Total:

More Dates

25/04/2024 – 27/04/2024

Price: $ 195.53

13/06/2024 – 15/06/2024

Price: $ 195.53

15/08/2024 – 17/08/2024

Price: $ 195.53

12/12/2024 – 14/12/2024

Price: $ 195.53

DAY ONE
Manipulating Data & Named Ranges
•    Paste Special
•    Transposing data
•    Importing text and delimiting by space, comma, or tab
•    What does naming a cell range mean?
•    Rules for naming cells and ranges
•    Naming cell range(s) in a worksheet
•    Named ranges within formulas
•    Navigating through workbooks using named ranges
•    Creating named ranges automatically based on cell values
•    Deleting named cells/ranges
•    Creating organizations
•    Removing organizations.

Excel Templates
•    Using templates
•    Creating templates
•    Opening and editing templates.

Formatting & Display Techniques
•    Formatting tables
•    Formatting tables using table styles
•    Formatting cell ranges using conditional formatting
•    Creating custom number formats
•    Freezing the top row
•    Freezing the first column
•    Freezing the top row and the first column at the same time
•    Hiding and un-hiding rows
•    Hiding and un-hiding columns
•    Hiding and un-hiding worksheets.

 

DAY TWO
Sorting & Querying Data
•    Sorting internal Excel databases
•    Custom sort options
•    Using AutoFilter to query data
•    Multiple queries
•    Removing filters
•    Top 10 AutoFilter
•    Filtering unique records
•    Advanced Filter.

Linking & Consolidating Data
•    Linking individual cells within a worksheet
•    Linking charts to data within a worksheet
•    Linking a cell range on one worksheet to another worksheet (within the same workbook)
•    Linking data on one worksheet to a chart in another worksheet (within the same workbook)
•    Linking data from one workbook to another
•    Linking a chart from one workbook to another
•    Copying data from Excel into a Word document
•    Linking data from Excel into a Word document
•    Copying a chart from Excel into a Word document
•    Linking a chart from Excel into a Word document
•    Consolidating data over several worksheets or worksheet pages.

DAY THREE
Charts Formatting Techniques
•    Changing the angle of pie chart slices
•    Formatting the chart axis fonts
•    Formatting the chart axis scales
•    Formatting the chart axis text orientation
•    Creating and positioning a chart title
•    Re-positioning a chart legend
•    Re-positioning chart data labels
•    Exploding the segments within a pie chart
•    Deleting a data series within a chart
•    Adding a data series to a chart
•    Modifying the chart type for a defined data series
•    Widening the gap between columns/bars within a 2-D chart
•    Inserting an image into a 2D chart (as a background)
•    Inserting an image into a 2D chart (to format a column or bar of data).

Protection & Security
•    Specifying a password for opening a workbook
•    Using the “read-only recommended” option
•    Removing a password from an Excel workbook
•    Protecting a worksheet or worksheet elements
•    Removing workbook protection
•    Allowing selective editing of a protected worksheet.

Other Courses