Microsoft Excel specialists

Webinar recordings


  Currently available recordings
Expand all

Excel formulas

  • Introduction to VLOOKUP, SUMIF, SUMIFS, range names, traps, best practice, and tips
    VLOOKUP is a much-used and most-useful function. This webinar is an introduction to VLOOKUP and covers a number of its common traps and solutions. SUMIF is another powerful function you should have in your arsenal. Use it to total data that meets certain criteria.
  • Advanced lookups: VLOOKUP, INDEX, MATCH, ISNA, T, traps, array formulas
    VLOOKUP is much used, but even advanced users fall into its many traps. This webinar covers the many problems and provides the solutions.
  • Beyond SUM: SUBTOTAL, IF, AND, OR, AVERAGE, COUNT, MIN, MAX and more
    A survey revealed that 99% of Excel users know only one function: SUM. Move well into the top 1% by learning 9 more functions: SUBTOTAL, IF, AND, OR, AVERAGE, COUNT, COUNTA, MIN and MAX.
  • Array formulas
    Unlock a whole new world of formulas. Create array formulas for tasks, which for ordinary formulas, are impossible or long-winded.
  • Formulas for accountants – Part 1
    Here are a variety of formulas for common accounting requirements. Whether or not you have exactly the same requirements, there is much to learn that will be useful elsewhere.
  • Formulas for accountants – Part 2
    This webinar covers various topics in cash-flow forecasting and actuals reporting. It includes the topics of: (A) Cash from revenue, debtors, expenses and creditors. (B) The calculation of debtor days. (C) The calculation of depreciation (straight-line and diminishing value), but avoiding the need for carry forward balances. (D) Creating a rolling forecast. (E) And more.
  • File-linking and formula auditing
    Formulas that refer from one workbook to another, otherwise known as file-links, are something it seems that few people know how to set up correctly. The upshot is wrong values referred to and spreadsheets that are a nightmare to audit. It doesn’t have to be that way. This webinar shows you how to avoid these problems.
  • Date and time calculations
    Gain an in-depth understanding of how to work with dates and times. Learn how to calculate age, show working days and shade calendar holiday dates. Discover time formulas and formatting. Become familiar with many functions including NETWORKDAYS and the hidden DATEDIF.
  • The workings of formulas in-depth
    Formulas are fundamental to Excel, yet it is clear that many people are baffled by key aspects. For example, do you know why 20.3+0.9-20.3-0.9 is not zero? Do you know what a volatile function is? This isn’t a topic about the many different functions, though there are a number encountered. It’s about understanding the inner workings of Excel, creating reliable and understandable formulas, avoiding errors, speeding up and auditing.
  • D-functions and criteria
    If you need to total, say, the values of 50 different products, you could use 50 SUMIFS or 1 DSUM. This is the topic of D-functions (of which DSUM is one). SUMIFS has limited criteria, but the D-functions allow any. And with DGET, you can return, say, price list entries, but, unlike VLOOKUP, it warns you if the item located has a duplicate. Apply the same powerful criteria to filtering.

Macros

  • Macros introduction – Part 1
    Create simple macros in Excel to automate common tasks. Learn how to record, edit, and understand macro code. Add buttons and shortcut keys to run the macros.
  • Macros introduction – Part 2
    Create an import application by breaking a complex problem into three steps. For each of the steps, see how to record, edit, and debug the macro code, plus how to prompt the user to enter required details.
  • Macros introduction – Part 3
    Improve the import application. Prompt the user. Use If… Then… Else logic. Loop to process data.
  • Macros next step – Part 1
    Drawing on the skills learned in the Excel macros introduction webinar series, use other strategies to process worksheets and user entry.
  • Macros next step – Part 2
    Construct a UserForm and apply event procedures to enable drop-down lists, option buttons, and command buttons. See how to transfer data into the UserForm and return data to the spreadsheet.
  • Macros next step – Part 3
    Use query techniques to get data from an external source. See how to interface to another application by setting a reference and referring to application objects.

PivotTables

  • PivotTables introduction
    PivotTables are great tools for analysing data that’s in a table, data that is often imported from elsewhere. For example, very quickly create a report that shows sales by product down the page and by region across the page. AbleOwl's PivotTables introduction will have you up and running with PivotTables In Excel/2013.
  • PivotTables next step
    PivotTables are among the best tools to rapidly summarise large amounts of data, but are often underutilised.
    • How do you organise a date field into months or weeks?
    • You have Budget and Actual figures, but how can you display variance?
    • What happens when you have more than one PivotTable pointing to the same source data?
    Get more out of your PivotTables with AbleOwl’s PivotTables next step webinar for Excel 2007/2010/2013.
  • Power Pivot for Finance – part 1
    Meet the Power Pivot add-in, import external data and discover how you can sidestep gnarly VLOOKUPs to consolidate multiple data sources into a single PivotTable report. Begin learning the new DAX formula language and create calculated columns to serve as new PivotTable label fields and Slicers.
  • Power Pivot for Finance – part 2
    Leave behind clunky, unwieldy PivotTable calculations and messy workarounds: start building Power Pivot measures and using the myriad new and wonderful DAX functions. Discover how time intelligence functions make quick work of frustrating fiscal year calculations, and open the door to a whole new world of analytical possibilities.
  • Power Pivot for Finance – part 3
    Create a report to compare monthly and YTD sales figures between this year and last year. Along the way, discover time-intelligence traps (and the trick to outwitting them), add interactivity so users can decide whether to view values in thousands or millions, and whether they want to report on sales, costs or profits. Finally, add coloured KPIs to clearly indicate whether performance was good, bad or just plain ugly.
  • Power Pivot for Finance – part 4
    Powerful and useful though they may be, PivotTables have always suffered from extreme formatting inflexibility. If you want to insert rows, add intermediate calculations and format to suit your heart's desire – or if you're just finding Power Pivot measures intimidating and want to calculate in a familiar environment – meet Excel's CUBE functions, which give you the best of both worlds. Retain the interactivity of Slicers but relax those formatting restrictions to create the report of your dreams. *This webinar isn't limited to Power Pivot users – anyone with an OLAP data source can harness the power of CUBE functions.
  • Power Pivot for Finance – part 5
    Learn advanced Power Pivot concepts, such as defining a separate table of headings and explicitly allocating records to those headings within your PivotTable. Determine the local filter context of a cell to perform different calculations in certain portions of the PivotTable, and turn off +/- expansion for items where it's simply not appropriate.
  • Power Pivot for Finance – part 6
    Add all the bells and whistles to your cash flow report, such as expandable financial years, or the option to choose how many previous time periods to view with a simple Slicer. Amend function arguments based on filter context, and finally, summarise your dynamic cash flow report in an easy-to-understand waterfall PivotChart for better data insight.
  • Power Query for Finance
    Data rarely arrives in our inboxes in the format required; if you spend tedious hours each month manipulating and consolidating data, Microsoft's Power Query add-in may become your new best friend. Don't let its simple, friendly user-interface fool you: there's some serious welly in this engine. With a brand new formula language under the hood, it's no shocker that this little beauty serves as the perfect sidekick to analytical powerhouses like Power Pivot. What you may not realise, though, is that Power Query has just as much to offer when all you need is a way to speed up your daily data management chores in Excel.
  • Advanced PivotTables for Finance – part 1
    In this webinar, you have a downloaded source of detail actuals data and you need to produce a variety of financial reports. There is also budget data, and many of the reports need to show month and year-to-date actuals comparison versus budget. PivotTables have a wealth of features to get the job done quickly, but there is wealth of issues to be solved too. You learn the features you need and how to get round the limitations. You learn how to structure the application when the datasets are large and there are many different reports required.
  • Advanced PivotTables for Finance – part 2
    In this webinar, you have a downloaded source of detail actuals data and you need to produce a variety of financial reports. There is also budget data, and many of the reports need to show month and year-to-date actuals comparison versus budget. PivotTables have a wealth of features to get the job done quickly, but there is wealth of issues to be solved too. You learn the features you need and how to get round the limitations. You learn how to structure the application when the datasets are large and there are many different reports required.

Tips

  • Hidden shortcuts – part 1
    When we run public courses, the most frequent comment from attendees is that they really enjoyed learning all the fantastic shortcuts. They appreciate the speeding up of regular everyday tasks. It’s hard to learn most of these shortcuts by yourself, because they are usually not obvious. And there are so many of them. Here’s the first installment in a webinar series, and whether you consider yourself novice or expert, you’ll learn so much.
  • Hidden shortcuts – part 2
    When we run public courses, the most frequent comment from attendees is that they really enjoyed learning all the fantastic shortcuts. They appreciate the speeding up of regular everyday tasks. It’s hard to learn most of these shortcuts by yourself, because they are usually not obvious. And there are so many of them. Here’s the first installment in a webinar series, and whether you consider yourself novice or expert, you’ll learn so much.
  • 12 common big Excel disasters… and the solutions
    These are all errors that occur frequently. They are insidious because they are easily not noticed. Billions are lost because of them every year. Be aware and know how to avoid them.
  • Everyday Excel you should know – Part 1
    Operate Excel and move around efficiently. Enter, edit and manipulate data skilfully. Work with comments and objects effectually.
  • Everyday Excel you should know – Part 2
    Format rapidly and consistently for a professional look. Protect and preserve your data. Optimise viewing and printing. Customise Excel options to suit your needs.
  • Excel disasters 1-10…and the solutions
    In Excel, there are certain errors that occur frequently, and which are particularly insidious because they're easily overlooked. This webinar series takes a hard look at some of the worst Excel calamities and provides not only solutions, but also effective prevention techniques that will help you to avert spreadsheet catastrophe.
  • Excel disasters 11-20... and the solutions
    In Excel, there are certain errors that occur frequently, and which are particularly insidious because they're easily overlooked. This webinar series takes a hard look at some of the worst Excel calamities and provides not only solutions, but also effective prevention techniques that will help you to avert spreadsheet catastrophe.
  • Reconciliation – Part 1
    Compare data in two tables, allow for variations, merge two tables into one, and locate and remove duplicates in a single table.
  • Reconciliation – Part 2
    Synchronise sheets and manipulate the display to aid visual comparisons, reconcile invoices and payments, use tools to instantly complete many comparison tasks.

Graphical reporting and presentation

  • Charts introduction
    Excel makes it easy to present data in a chart. Learn the tricks to create various chart types that Excel has to offer.
  • Conditional formatting
    Need to add some punch to your Excel reports? Discover the Excel features that make your worksheets come alive.
  • Dashboards
    Step back from the detail and gain a bird's-eye view of your dept/company's health. Discover the tools and methodology you need to create dashboard reports in Excel.
  • Charts for accountants
    Electrify your data with charts that transmit information faster and more powerfully than numbers alone. Discover tips, tricks and traps of compelling charts. Learn Waterfall and other charts specific to accounting.
  • Charts next step for Excel 2007/2010
    If a picture is worth a thousand words, a good chart is worth at least two thousand. Take full advantage of Excel’s advanced charting capabilities to create persuasive, informative charts that’ll have your colleagues clamouring for your Excel expertise and your boss eyeing you for that new corner office.
  • Include Excel data in Word and PowerPoint
    Easily incorporate Excel data into Word to create your management/board reports. See how to create professional PowerPoint presentations with live Excel data.
  • Conventions and Clarity - Individual sheets
    If you open a book, you can find your way around it. If you open a workbook, you probably can’t. That’s because, the book has many conventions applied and the workbook does not. You waste much time as a result. Apply conventions picked up from this webinar and, before long, you will recoil in horror at what you did before. It’s better still if you can get your whole department singing from the same songbook. Most probably the biggest return for your time invested in Excel training is from learning how to standardise your spreadsheets.
  • Conventions and Clarity - Workbook structure
    See above
  • Conventions and Clarity - Charts and tables
    See above

Robust applications

  • Tables – Part 1
    Tables reduce the risk of spreadsheet errors, such as omitted data and corrupted formulas. Use Tables so that formulas, PivotTables, Charts and range names automatically extend to include newly-inserted rows and columns.
  • Tables – Part 2
    Tables reduce the risk of spreadsheet errors. Create Table calculated columns with robust Table reference formulas: running totals, differences from the row above and row indices. Make references absolute or relative.
  • Crosschecks – Part 1
    Catch many spreadsheet errors. Quickly set up basic crosschecks and structure them so that they cannot be overlooked. Set a tolerance to avoid false positives. Quickly check whether there are any crosschecks anywhere in the workbook.
  • Crosschecks – Part 2
    Catch many spreadsheet errors. Discover a number of advanced crosschecks such as for warning of missing report items, warning of missing inputs, checking that the items in one list are included in another and checking that items on consolidated sheets match.
  • Protection, validation, and troubleshooting
    Prevent unwanted changes by protecting spreadsheet formulas while still allowing entry into input cells. Apply Data Validation to allow selection of a cell value from a drop-down list. Also in this webinar, see how to solve common spreadsheet problems such as overly-large files and unreliable behaviour.

Consolidation

  • Consolidation
    You have data for departments, products or other units, all in multiple sheets or workbooks. You want to total and combine that data to create various reports. In this webinar, you learn the different ways to do that and understand which method is best in different circumstances.

Business planning and investment analysis

  • Investment analysis
    Know how to use Excel to analyse the profitability of projects. Make use of IRR, NPV, XIRR and XNPV. Avoid common traps. Use financial functions to calculate loan interest, principal and payments.
  • Long-term planning and sensitivity analysis
    Learn long-term financial modelling in which debt and interest calculations are a key part of the model. Also learn how to use Data Tables for running a model multiple times and collating key results into a table.
  • Monte Carlo simulation
    Analyse a project by running a model many times with randomness in many variables. In other words, simulate the real world. From that, determine the likelihood of success. It’s not as difficult as you might think.
  • Forecasting
    You have business data, but need to determine the trend so that you can forecast. What type of trend do you expect? How do you decide whether to fit a linear or a more complex trendline? Once you have fitted a trendline, how can you assess its performance? Is it even a good fit? Discover the answers and how easy forecasting is in Excel.
  • Solver, Goal Seek, Scenarios
    Many business problems have more than one potential solution; of course, you only want the very best solution. What staff schedule minimizes wage costs? What is the most profitable product mix? How can you compare multiple solutions to determine the optimal course of action? Learn to use different Excel tools – Solver, Goal Seek, Scenarios and DataTables – in order to make the best decision, every time.

Working with data

  • Data manipulation
    You produce reports from a weekly data import. However, the data is never an exact fit for the report requirements. How do you fix up inconsistencies in the data, such as capitalisation and unwanted spaces? How do you organise your data into the order you desire, or display only those records you want in the report? Learn how easy it is to fix up and manipulate your data with Excel.
  • Importing and manipulating text files
    Don’t rekey data when you could import it from text files. To get the data into the format needed, you will learn string formulas and manipulation tricks.
  • Getting external data directly – Part 1
    Better still than importing data from downloaded text or Excel files, extract directly from source. Most programs provide the necessary ODBC drivers to enable direct extraction. Set criteria to return the records wanted. Discover the various tools Excel provides, including Microsoft Query.
  • Getting external data directly – Part 2
    Go beyond what MS Query can do with SQL commands. Learn basic SQL.

Collaboration

  • Collaboration
    You may have a file which several people need to have access to and change if they wish. How do you organise that to operate in the most efficient way? The use of email is unlikely to provide a good solution. The alternatives of local-server or cloud hosted files provide the likely solutions. This webinar covers Excel's Share Workbook and, for collaborating in the cloud, Microsoft OneDrive. Users of other cloud drives, such as Google Drive and Dropbox, will find that details vary, but the same general concepts apply.
Confirm
Do you want to play webinar recording: Label