Upgrading Your Skills with Excel

TABLE OF CONTENTS

CHAPTER 1
Calculating rightly

1.1 The working environment and formulas

1.1.1 Simple calculations

1.1.2 Workshop : a body mass index calculation

1.1.3 Some reminders about the formulas

1.1.4 Extracting simple statistics

1.2 Formulas with dates

1.2.1 Difference between two dates

1.2.2 Information about formulas

1.2.3 Adding a number of months to a date

1.2.4 The DATEDIF function for seniority calculations

1.2.5 End of month

1.2.6 Calculation according to working days

1.3 The IF function

1.3.1 Comparing a budget and expenditures

1.3.2 Nesting IF functions

1.3.3 The Lookup function

1.3.4 IF and AND

1.4 The $ sign in formulas

1.5 Formulas with text

1.5.1 A strange date that turns into a valid date

1.5.2 The Flash-fill feature

CHAPTER 2
Conditional formatting

2.1 Highlighting values

2.1.1 Clearing rules

2.1.2 Using a formula to highlight cells

2.1.3 Highlighting values

2.1.4 Identifying duplicates

2.1.5 Highlighting dates

Workshop1 : displaying all the dates for year 2018

Workshop2 : Colouring the rows for year 2019

2.2 Top/bottom rules

2.3 Colouring empty cells

2.4 The Data Bars

2.5 Icon Sets

2.6 Colour scales

CHAPTER 3
Filtering, sorting out, layouts and printing

3.1 The filters

3.1.1 Creating a filter

3.1.2 Data with a format as table

3.1.3 The Total row

3.2 Sorting out

3.3 Layouts and Printing

3.3.1 The viewing dialogue box

3.3.2 The layout settings

3.3.3 Setting the print area

3.3.4 Inserting page breaks

3.3.5 Viewing page breaks

CHAPTER 4
Charts

4.1 Columns charts

4.2 Line charts

4.3 The trend

4.4 Pie chart

4.5 Radar charts

4.6 A bubble graph

4.7 A Geo chart

4.8 The Sparkline chart

CHAPTER 5
Advanced functions

5.1 The VLOOKUP function

5.1.1 Finding a text value

5.1.2 The #N/A error helps

5.1.3 Finding a number in a scale

5.1.4 Searching with a joker *

5.1.5 VLOOKUP with two conditions

5.2 The INDEX/MATCH functions

5.2.1 The INDEX function

5.2.2 The MATCH function

5.2.3 INDEX and MATCH together

5.3 Statistics with SUMIF and COUNTIF

5.4 The SUMPRODUCT function

5.5 Locking formulas

5.5.1 Locking a sheet with the menu

5.5.2 Leaving cells free to edit

5.5.3 Locking formulas with matrix

5.5.4 Matrix functions.

5.6 Target value and data table

5.6.1 The target value

5.6.2 Data table

Workshop 1 : A single-entry data table

Workshop 2 : A double-entry data table

CHAPTER 6
Pivot tables

6.1 The structure of the source table

6.1.1 Some structural constraints

6.1.2 Creating a Pivot Table

6.1.3 Designing a Pivot Table

6.1.4 Refreshing the PivotTable

6.1.5 Counting with a Pivot Table

6.1.6 Viewing the rows at the source of a result

6.2 Calculation functions