EN
0
Basket

Your basket is empty

background section

Excel : advanced II

There’s a reason why Excel is the most widely used spreadsheet in the world: it’s designed to help you work more quickly, efficiently and effectively. Of course, to truly tap into the awesome power of Excel, you  need to know more than the basics. This course takes your Excel knowledge and abilities to a higher level.
 
Do you want to take your Excel skills even further? Follow up this course with another one from the range of Excel courses at Cevora.
 

  • Training type

    Classical

  • Duration of training

    2 days

  • Available languages
    EN 
  • Training code

    1664EB2

What do you learn?

 
  • You have no questions or doubts when it comes to using What-If analyses, even in conjunction with the Goal Seek, Solver and Scenarios tools;
  • You know the why, where, when and how of using array formulas, user-defined formulas, as well as cube functions and even formulas based on conditional formatting;
  • You record and use macros to speed up your work processes.

Target group

You have a reasonably advanced knowledge of Excel, but would like to unlock a few more of its secrets.

Required prior knowledge

Your knowledge of Excel is advanced enough for you to use formulas and PivotTables without a second thought. The Cevora course  – Excel: Advanced I is the perfect foundation going into this course.

Overview of the programme

1 WHAT-IF ANALYSIS

1.1 Goal seek

1.2 Running simulations using input tables

1.3 Scenarios

1.4 Solver

 

2 ARRAY FORMULAS

2.1 Array formulas in detail

2.2 When is it necessary to insert an array

2.3 Formulas requiring several cells

 

3 CONDITIONAL FORMATTING BASED ON FORMULAS

3.1 Highlighting duplicate values

3.2 Hiding error values

3.3 Highlighting a value occurring within a range

3.4 Highlighting weekends and holidays

 

4 CREATING A TEMPLATE

4.1 A double input table

4.2 Applying conditional formatting

4.3 Adding a spin button

4.4 Saving the workbook as a template

 

5 USING A TEMPLATE

6 EXERCISES

7 USER-DEFINED FUNCTIONS

7.1 Discount

7.2 Multiple vertical lookup

7.3 ARLookup

 

8 CUBE FUNCTIONS

8.1 Cube value

8.2 Cube member

 

9 INTRODUCTION TO DASHBOARDS

9.1 Variables

9.2 Formulas

9.3 Inserting popular controls

9.4 In practice

 

10 PROTECTION

10.1 Protecting cells

10.2 Hiding formulas

10.3 Protecting a worksheet

10.4 Unprotecting the sheet

10.5 Protecting a workbook

 

11 FINDING CELLS WITH FORMULAS

12 THE INQUIRE ADD-IN

12.1 Activating the add-in

12.2 Identifying and analysing the location of formula cells

 

13 EXERCISES

14 RECORDING A MACRO

14.1 What is a macro?

14.2 The Developer tab

14.3 Recording a macro

14.4 Running a macro

14.5 Deleting or editing a macro

14.6 Relative and absolute macros

 

15 LINKING A MACRO TO A BUTTON

15.1 Button on Quick Access Toolbar

15.2 Button on the ribbon

15.3 Form control on the worksheet

15.4 ActiveX control on the worksheet

15.5 Linking macros to AutoShapes

 

16 SAVING FOLDERS WITH MACROS

17 MACRO SECURITY

17.1 Enabling macros when the message bar is displayed

17.2 Enabling macros in Backstage view

17.3 Changing macro settings in the Trust Center

17.4 About macro settings

17.5 Adding, deleting or modifying a trusted location for your files

 

18 VISUAL BASIC EDITOR ENVIRONMENT

18.1 Comment

18.2 Sub … End Sub

18.3 With … End With

 

19 VBA AND MACRO BUILDING BLOCKS

19.1 Message boxes

19.2 Input boxes

19.3 Variables and constants

 

20 BUILDING STRUCTURE WITH MACROS

20.1 Sequence

20.2 Selection

20.3 Iteration

20.4 Multiple choice

 

21 RESERVED MACRO NAMES

21.1 Auto_Open or Workbook_Open

21.2 Auto_Close or Workbook_Close

 

22 DEVELOPING A FUNCTION

22.1 Creating a procedure

22.2 Calling a function

22.3 Finalising a function

 

23 THE DIFFERENCE BETWEEN MACROS AND FUNCTIONS

23.1 Calling macros or functions

23.2 Using arguments

23.3 Functions return a value

23.4 Macro Call

 

24 SHARING MACROS AND FUNCTIONS EXCEL ADD-IN

24.1 Excel Add-in

24.2 Activating Add-In

24.3 Using new functionalities