INHOUSE EXCEL TRAINING CONTENTS

What you will learn in our inhouse Excel training | 7h

Data analysis: Working effectively with tables and lists

• Table formatting procedure that you can use every time you export data to Excel (formatting, fixings, visual hierarchy)
• Apply selected values of a table to a calculation simultaneously (e.g. divide all values of a column by 100)
• Rotate tables
• Search for specific content in tables
• Compare tables and find e.g. duplicates or discrepancies
• Sort options according to several criteria
• Number filter / word filter
• Filter options with and / or
• Highlight values with conditional formatting (rule to highlight cells, use icons, heatmaps. etc.)
• Relative and absolute references
• Spark lines
• Transfer data from other formats (e.g. PDF) into Excel
• Use pivot tables effectively
• VLOOKUP (very practical for searching in tables, e.g. display the price for a certain product from a product list and merge data from several worksheets)
• INDEX MATCH as an alternative to VLOOKUP
• HLOOKUP
• Keyboard shortcuts that save you a lot of time

Calculations: Working with functions

• Create dropdown selection fields, e.g. to offer variables
• Target value search, e.g. for result planning
• Break-even analysis to determine the break-even point
• Creating scenarios with estimator and trend functions
• Calculate with date and time periods
• Sum product
• Logic (IF-function)
• Nest functions (e.g. second and third IF-function to calculate a commission scale)
• IF function with AND / OR
• COUNTIF using the example of evaluating a survey
• Left, Right, Part (cutting out cell contents)
• Length, count, count2
• Concatenate, join and text in columns
• Conditional formatting depending on other cells, e.g. if a certain value in cell X is exceeded, then cells are highlighted in colour
• Frequency (e.g. how many customers have ordered between 5 and 10 times?)

Data visualization: How to communicate data effectively

• Recommendations on how to choose the most appropriate chart type in each case
• Customize charts effectively (color, font, axes labels, scales, order of data, etc)
• Show developments as a waterfall model
• Diagrams with 2 axes (secondary axis) to show a third dimension (e.g. a trend line)
• Histograms (summary of data)
• Project plan (Gantt chart)
• Integrated bar charts (e.g. ideal for budget presentations)
• Before – after comparisons with a dot line chart (show how something has improved / deteriorated)
  Benchmarks