Less is more; this course is designed to demonstrate how with just a few advanced formulae, or maybe a quick macro, we can do so much more with Excel. All this without crashing the document or pulling your hair out.
Throughout the course, you will work with a variety of different Excel workbooks to complete typical Excel tasks, such as adding charts or conditional formatting, in a way that dynamically updates with your data so that you don’t have to keep coming back to the file to make changes. We’ll also cover off some common reasons that workbooks crash or take ages to load, and provide some tips on improving workbook performance.
Do you spend much of your day in Microsoft Excel? Do you work with advanced formulae and functions and wonder if there’s a better way? Are you keen to learn more about what Excel has to offer? If you can answer yes to any of those questions, then you’ll find this course useful.
This course is for advanced Excel users. We recommend attendees have knowledge equivalent to the topics covered in our Excel Essential Skills and Excel Timesaving Features courses, OR have:
- A thorough understanding of formulae including absolute references ($A$1).
- A thorough understanding of using Excel’s common functions such as AVERAGE, MAX, COUNT, IF and the function wizard.
- The knowledge to create and use range names.
- The knowledge to create and manage Conditional Formatting rules.
You will leave this course armed with the knowledge to enhance your existing Excel workbooks through:
- improved graphical representation of your data using charts
- increased functionality and reliability using formulae
- dynamic data ranges and updates using macros and formulae
Sharing a Workbook: Collaborate and share Excel files effectively, with or without Office 365.
Selecting the Correct Chart and Data: Explore all the chart types available in Excel and discuss best practice considerations for when to use each type.
Editing Charts & Understanding Series: When you realize that you selected the wrong data for your chart, but don’t want to start from scratch.
Trendlines and Error Bars: Analyse and interpret your chart data and display margin of errors.
Using Formulae in Conditional Formatting: Automatically highlight the entire row based on the value of a single cell.
Using Formulae in Data Validation: Ensure consistency with data entry by enforcing unique values or text only, and more.
Using Formulae in Names: Use this trick to create dynamic ranges, enabling your charts and reports to pick up on new data automatically.
Recording Macros: Save yourself some time by turning three clicks into one with simple macro recordings.
Basic Editing of Macros: Recorded macros almost always need a few small changes to be useful. Learn how to decipher the VBA code behind the macros you just recorded.
Using Range Names: Make sure your macro updates the right cells by using range names
Relative/Absolute Mode: You’ve heard of relative and absolute references (A1 vs $A$1), how does that apply to macros?
Recording AutoSum for a Varying Range: See how the use of keyboard shortcuts can help make your macro more dynamic.
Creating Macro Buttons: Create a custom button in the ribbon for your new macro.
Optimising Workbook Performance: Discuss common reasons that workbooks can be slow and clunky, and explore a few tricks to fix them.