Home / Non-Accredited / Microsoft Office & Computer Skills / Analysing Data with Excel (2 days)
Quick Look Course Summary:Analysing Data with Excel (2 days)
-
Next Public Course Date:
-
Length: 2 day(s)
-
Price (at your venue): 1 Person R 10,326 EX VAT 3 Person R 7,594 EX VAT 10 Person R 5,537 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Analysing Data with Excel (2 days)
Turn raw exports and scattered spreadsheets into clear, checked answers to business questions – and do it again next month in minutes.
Most business data arrives in Excel the hard way: a CSV export from the accounting or ERP system, a list from a supplier, last month's report in another workbook. Before anyone can answer a question, someone has to import it, clean it, line it up and check it – often by hand, every month. This practical Excel data analysis course teaches delegates to do that work quickly and reliably, then turn the result into an answer a manager can act on.
Day one is about getting data into shape: importing and exporting text and CSV files, cleaning with Power Query, consolidating and linking data across sheets and workbooks, filtering large lists, combining and comparing data sets with lookups, and recording a macro for a routine that repeats. Day two is about answering questions: building a spreadsheet solution to a business problem with SUMIFS, COUNTIFS and logical formulas, testing it with what-if tools, connecting to external data that refreshes, and presenting the findings in clear charts and a one-page summary.
Exercises use realistic sales, stock, HR and finance lists, and delegates can bring their own files to the closing case study.
Why this course
For your organisation
- Monthly reports produced in minutes instead of hours
- Fewer errors from copying, pasting and re-keying
- Decisions based on reconciled, checked data
- Analysis that colleagues can refresh and repeat
For your delegates
- A repeatable routine for importing and cleaning messy data
- Confidence matching and comparing large lists
- Formulas and what-if tools that answer real business questions
- Charts and summaries that make the answer obvious
What delegates will be able to do
- Import and export text and CSV files without losing leading zeros, dates or decimals
- Clean and reshape data with Power Query so the steps refresh next month
- Consolidate and link data across worksheets and workbooks
- Filter large lists with AutoFilter, Advanced Filter and slicers
- Combine and compare data sets with XLOOKUP, INDEX and MATCH, and conditional formatting
- Record and run a macro to repeat a routine data task
- Build a spreadsheet solution to a business question with SUMIFS, COUNTIFS and logical functions
- Test assumptions with Goal Seek, scenarios and data tables
- Present findings with well-chosen charts, sparklines and a one-page summary
Who should attend
Administrators, analysts, coordinators, supervisors and managers who already use Excel every day – entering data, writing basic formulas and creating simple charts – and now need to analyse larger or messier data and report on it. Delegates should be comfortable at Excel intermediate level.
Course outline
Day 1 – Getting data into shape
Importing and exporting data
- Opening and importing text and CSV files
- Delimiters, data types and keeping leading zeros
- Thirteen-digit ID numbers, phone numbers and dates that Excel changes
- Regional settings and the comma versus full stop decimal problem
- Exporting data cleanly for other systems
Cleaning data with Power Query
- Connecting to a file or a folder of files
- Removing blanks, duplicates and unwanted columns
- Splitting, merging and trimming text
- Unpivoting report-style layouts into a usable list
- Refreshing the query when next month's file arrives
Consolidating and linking data
- 3-D formulas across identical worksheets
- The Consolidate tool for data from several sheets or workbooks
- Linking workbooks and managing links
- Appending monthly files with Power Query
Filtering and managing large lists
- Excel Tables and structured references
- Multi-level sorting and custom lists
- AutoFilter, Advanced Filter and criteria ranges
- Slicers on tables
- Data validation to keep new entries clean
Combining and comparing data sets
- XLOOKUP and INDEX with MATCH
- Matching two lists and finding what is missing
- Highlighting differences with conditional formatting
- Removing duplicates safely
- UNIQUE and FILTER for quick extracts
Automating a routine with a macro
- Recording a macro for a repeated clean-up
- Relative and absolute recording
- Running a macro from a button
- Saving and opening macro-enabled workbooks safely
Day 2 – Answering business questions
From question to spreadsheet solution
- Framing the business question and the result needed
- Planning inputs, calculations and outputs on separate sheets
- Working across several worksheets
- Checking the finished spreadsheet against the question
Formulas for analysis
- SUMIFS, COUNTIFS and AVERAGEIFS
- IF with AND and OR, and IFS
- Date and text functions to group data by month, region or category
- Handling errors with IFERROR
- Named ranges for readable formulas
What-if analysis
- Goal Seek for targets and break-even points
- Scenario Manager for best, expected and worst cases
- One- and two-variable data tables
- Summarising scenarios for a decision
External data and supporting objects
- Connecting to external data sources and refreshing them
- Importing data from another workbook or a web page
- Adding notes, shapes and screenshots to explain a report
Presenting the findings
- Choosing the chart that answers the question
- Combo charts and trendlines
- Sparklines and conditional formatting for trends at a glance
- A quick PivotTable summary
- Building a one-page summary sheet
Case study
- Analysing a full data set from import to summary
- Peer review of results
- Applying the method to your own data
How it is delivered
Two days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20. Hands-on throughout in Excel for Microsoft 365 or Excel 2021 and later; laptops with the software can be supplied on request. Workbook and practice files included.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Frequently asked questions
Can you run this course at our offices?
Yes. Most groups are trained at the client's premises anywhere in South Africa; we can also host it at a BOTI venue or deliver it live online.
How many people can attend?
From one delegate to groups of 20. The price per delegate drops as the group grows – see the price table, or ask us for a quote.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance. If you need a credit-bearing route, ask us about BOTI's QCTO End User Computing skills programmes.
How is this different from the pivot table and data analytics courses?
Microsoft Excel Pivot Tables (1 day) and Excel Pivot Tables, Dashboards and Power Pivot (2 days) go deep into pivot reporting. Data Analytics Fundamentals (3 days) covers the analytics process and a first Power BI report. This course stays in Excel and concentrates on preparing, combining and testing data to answer business questions.
Which version of Excel do we need?
Excel for Microsoft 365, or Excel 2021 or later. On older versions the facilitator shows the alternatives to XLOOKUP and the dynamic array functions.
Can we use our own data?
Yes. Send us a sample export beforehand (with personal information removed or masked) and the facilitator builds it into the case study.