Advanced Excel is designed to create complex models involving multiple linked spreadsheets and files, Data validation, Conditional Formatting, Understanding and Using Formulas, functions, Nested functions, Subtotaling data, referencing function, manipulating data with Pivot Tables and Power Pivot, Consolidation of data, Managing Data with Tables, sharing workbooks, protecting workbook, What-If. Excel’s traditional formulas do not work on filtered data since the function will be performed on both the hidden and visible cells. To perform functions on filtered data one must use the subtotal function. The syntax is SUBTOTAL(functionnum, rangereference1, rangereference2.)The following functions may be performed with the subtotal. Advanced Excel Essentials is unlike most technical Excel texts in that it felt more like I was attending some sort of TED Talks lecture than skimming through a bunch of tutorials. Jordan Goldmeier presents some great technics in this book that are applicable to many real-world business situations and can easily be implemented. Advanced Excel users know how to gather, structure & present their data so that it looks impressive. Good understanding of Excel features like Power Query (Get & Transform Data), Tables, cell styles, formatting options is necessary to make awesome Excel workbooks. Figure drawing for fashion design elisabetta drudi tiziana paci pdf. Resources to learn Data, Tables & Formatting What is Power Query? This Microsoft Excel courses will take you through 4 levels of Microsoft Excel topics (Beginner, Intermediate, Advanced and Macros/VBA). If your looking to take your Excel skills from beginner to advanced level and beyond, then this course is for you.
This practical, 3 day Microsoft Excel Advanced Training Course focuses on the MS Excel skills that the typical business user would require.
It is run on a monthly basis in Midrand to facilitate delegates from Pretoria and Johannesburg. Gelsprinter driver download for windows.
- Next course : 10-12 May 2021
- Duration – 3 Days
- Skill Level >3.5
It is suitable for Intermediate and Advanced Excel users, but the best way to check if this is the best MS Excel course in Johannesburg for you is to do the Free MS Excel Skills Assessment first.
The next available course date is 10-12 May 2021. For more dates have a look at the training calendar for the various MS Excel courses we offer. We also offer virtual MS Excel courses.
Some of the key outcomes of the 3 Day Advanced MS Excel public course will include:
- Using the new tools that Microsoft has introduced
- Easily cleaning up data received to report on
- Understanding and making use of Pivot Tables, to turn data into reports
- Reporting findings graphically via charts and other visualisation techniques
- Ability to create an automated reporting pack that changes month on month with no effort
- Perform partial lookups where the words aren’t exactly the same
- Get dates to work how you expect them to.
- For a full listing see the contents below
Delivery Methods- Public Microsoft Excel Advanced Training Course
Live MS Excel Training options
- Public courses for Johannesburg and Pretoria
- Onsite- We can provide the training onsite to corporate groups- for more information click the request for information button below.
- Virtual training to your staff (see more)
Online MS Excel Intermediate Training
- If you are not in South Africa, or prefer to do the course online, have a look at combining the the Online MS Excel Intermediate Course and the Online MS Excel Advanced Course
Course Outline
Ultimate Excel Tool
- Flash Fill- Your new best friend?
MS Excel Tips and Tricks
- Viewing many parts of the spreadsheet (at the same time)
- Don’t hide rows- group them rather
- See sheet names in portrait style
- Selecting ranges with mouse and arrow keys- short cuts
- Enter date and time stamps- unchanging
- Doing Quick Calculations in a spreadsheet
- The Quick Access Toolbar
Filtering and Sorting
- Data sorting-Revision, colour and icon sorting
- Sorting the columns instead of the rows
- Create a custom sorting list
- Data Filtering
- Remove rows with the Filter
- Remove duplicate rows
- Use Advanced Filter for repeating filters
The Specials- GOTO and Paste
- GOTO Special- find comments, values, formulas and more
- Find errors, conditional formatting and data validation
- Copy Visible Cells Only
- Fill in all the blanks
- Paste Special- Values, Formulas with/without formatting
- Paste Special- convert negatives to positives
- Switch rows to columns and columns to rows
- Paste without overwriting blank cells
- Re Use the Format Painter
Tools for text
- Find Replace- standard uses
- Find Replace- Change formatting on many cells
- Remove between brackets or characters
- Single Column to Multiple Columns
- Import CSV’s into the correct columns
- Find partial names or between characters (fuzzy logic)
- Change the $ signs on multiple cells with FIND/ REPLACE
- Import CSV but correct for ‘numbers’ that start with zero e.g. credit card numbers
Pivot Tables
- Required data format for Pivot tables
- Create a Pivot Table- Step by Step
- Inserting Values into a Pivot Table
- Enhancing the values e.g. percentage of another column
- Show the details of a number in the Pivot Table
- Laying out the Pivot Report in completely separate columns
- Working with the rows in a Pivot Table- dropdown
- Working with the rows in a Pivot Table- field settings
- Normalize Pivot Reports to use elsewhere as a copy paste
- Working with the Columns in a Pivot Table
- Working with the Filter in a Pivot Table
- Automatically duplicate your Pivot Tables with different selections
- Slicers in a Pivot Table
- Control all Pivot Tables and Charts at once
- Timelines in a Pivot Table
- Grouping Dates into days, months, quarters and years in a Pivot Table
- Grouping Dates into weeks in a Pivot Table
- Grouping Text in a Pivot Table
- Grouping Numbers in a Pivot Table
- Pivot Charts in Excel
- Problems with grouping in Pivot Tables
- Protect the source data of a pivot table
- Add a calculation INSIDE a Pivot Table
- Conditional Formatting within a Pivot
- Get information out of a Pivot into a cell
- Switching off the GETPIVOTDATA
- Add up ITEMs within a Pivot Table Field
Conditional Formatting
- Change cell colour based on value
- Change color based on benchmark in another cell
- Highlight all above average cells
- Add comparative bars into cells
- Create heat maps in Excel
- Add risk robots, milestone, up and down icons to Excel cells
- Control the heat maps, icons and data bars in conditional formatting
- Conditional Formatting- Linking to different cells
- Control what is entered in an Excel cell- Data Validation
- Control the inputs in a cell based on another cell
Function and Formula Basics
- Tip- Opening Frequent files
- Enter formula into many cells (at the same time)
- Function Wizard
- Auditing Toolbar
- Referring to fixed cells
- Using the F4 shortcut for $ signs
- Dollar Sign ($A$1) Examples
- The basic “IF” function
- How to (safely) build a nested IF formula
- IF this AND this AND this is True
- See the numbers behind a formula
- Change formula across many sheets at the same time
- Update the calculations on only one sheet (for large spreadsheets)
Text Functions
- Fitting text inside a cell
- Quick entry of rows of data
- Quick Drop Down List
- Printing Tips
- Get Excel to read out the text and numbers
- Join text from many cells
- Extract parts of the text from cells
- Force Excel to see the value (instead of text)
- Change text to lower, UPPER or Proper case
- Remove unnecessary spaces
- Create a find/ replace formula
- Find where a certain character is in a cell
- Force excel to treat number as text
Key Lookup Functions
- VLOOKUP explained in simple terms
- Build your first VLOOKUP
- The many uses of VLOOKUP
- Vlookup approximate match (True instead of False)
- Vlookup partial text from single cell- fuzzy lookup
- Automatic Sorting in Excel
- Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
SUM, COUNT, AVERAGE IFS
- Sum, count or average if certain cells match
- Sum, count or average cells e.g. bigger than
- Sum, count or average with many conditions
- Sum, count or average with partial matches
Date functions and issues
- How Excel Handles Dates
- Safest way to capture a date in Excel
- Pull the day, month or year out of the date
- Create a VALID Excel date with the day, month and year
- Calculate the proper end of month
- Determine what day of the week a date is
- When is the next workday
- How many working days between dates
- What week number is a date
- Make Excel recognise a date
Charting In Excel
Microsoft Excel Advanced Tutorials
- Excel Charting introduction
- Create a chart
- Align chart to the grid lines
- Chart doesn’t look right? First thing to try
- Customise the chart title, area, legend and more
- Vertical Axis- force the scale, reverse the order, labels and more
- Horizontal Axis- dates vs text, reverse order, show all labels
- Series- overlapping, big and small series, gaps
- Add more series to the chart- 3 ways
- Show big and small numbers on the same chart (and be able to see them)
- Show months, quarters AND years on the chart axis
- Bar Charts
- Pie Charts
- Area Charts
- Radar Charts (to compare projects?)
- Show mini trend charts for large reports
- Copy the format of one chart to another chart
- Remove Zeros from chart labels
- Combine a column with a line chart (or other combinations)
- Stop charts stretching when the column width changes
- Customise the series marker to your own image
- Add linked commentary directly to the chart
- Add commentary to the axis
- Add commentary to labels and call outs
- Add commentary to labels for older versions of Excel
- Hiding a series with NA()
- Create a waterfall chart (pre Excel 2016)
- Create a waterfall chart (Excel 2016 and higher)
- Understand and use XY charts e.g. Risk Register
- Change the default colour scheme in charts
- Create a chart template for re-use
- Add a trendline to a chart
- Benefit of using a table for chart data
- Link Excel chart to PowerPoint or Word
Feedback & Who Should Attend
- I’m glad I invested my money and time on this course, it was worth it.I also like the fact that i will be assisted even after completing the course. I walked out a different lady, just confirming what De Wet said.- Kefiloe M.
- It dealt with my daily challenges at work- IS Management Accountant.
- There was nothing I did not like about the workshop. Adrian was fantastic- Accountant.
- This course really add significant value to my life as a data analyst- Lonmin
- … first the assessment is a good tool to determine that the trainee is allocated to the correct training, secondly, although the training is intense the content is brilliant and very well presented. Being able to express real live occurrences during the training makes for understanding of applications. All Krones personnel that have attended this training has had their skillset enhanced. Being proficient in excel has allowed the flow of work from department to department to become more and more efficient and not to mention, look more professional. Audit Excel is my only choice for Excel Training.- Debbie L- Krones
Typical Attendees
- All persons using Excel spreadsheets. Professions that have attended include accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers
Advanced Ms Excel Formulas
Pre Requisite (s)
We recommend that all delegates should, using our free MS Excel Skills Assessment, have a skill score of >3.5 with a level of Beginner-to-Intermediate or Intermediate (get your delegates to do the free MS Excel Skills Assessment to make sure this is the correct level of training).
This is a very hands-on, non-intimidating, course – delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel’s functions.