Advanced spreadsheets – Microsoft Excel 2010, handbook

Download (PDF, 3.19MB)

CONTENT:

Foreword

1. DESIGN

1.1  CELLS

Apply automatic formatting/table style to a range of cells

Apply conditional formatting based on cell content

Create and apply custom number formats

1.2  WORKSHEET

Copy, move worksheets between spreadsheets

Split window. Move, remove split bars

Hide, show rows, columns, worksheets

2. FUNCTIONS AND FORMULAS

The use of functions and formulas TODAY, NOW, DAY, MONTH, YEAR

Use mathematical functions: ROUNDDOWN, ROUNDUP, SUMIF

Use statistical functions: COUNTIF, COUNTBLANK, RANK.

Use text functions: LEFT, RIGHT, MID, TRIM, CONCATENATE

Use financial function: FV, PV, PMT

Use functions for references and search: VLOOKUP, HLOOKUP.

Use function database: DSUM, DMIN, DMAX, DCOUNT, DAVERAGE

Use level nested function

Use 3-D references in the function sum

Use mixed references in formulas

3. CHARTS

3.1  CREATING CHARTS

Create a combined line-bar chart

Add a secondary axis to a chart

Change the chart type for a defined data series

Add, delete a data series in the chart

3.2  DESIGN OF FIGURES

Change the position of the chart title, legend, data title

Change the value axis: minimum, maximum number to display, major interval

Change display units on axis value without changing data source: hundreds, thousands, millions

Format columns, bars, plot area, chart area to display an image

4. ANALYSIS

4.1  USE OF TABLES

Create, modify a pivot table/datapilot

Modify the data source and refresh the pivot table/datapilot

Filter, sort the data in a pivot table / swivel chart

Automatically, manually group data in a pivot table / chart and rename groups

Use the table with one or two inputs / multiple operations

4.2  SORTING AND FILTERING

Sort data by multiple columns at the same time

Create a customized list and perform a custom sort

Automatic filter

Change the advanced filter options in the list

Use an automatic calculation of subtotals

4.3  SCENARIOS

Create scenario

View, edit, delete scenarios

Create a scenario summary report

5. EVALUATION AND SUPERVISION

5.1  EVALUATION

Set, edit validation criteria for data entry into a range of cells such as integer, decimal, list, date and time

Enter input message and the error message

5.2  SUPERVISION

Subscribe precedent, dependent cells. Tracking errors.

Show all formulas in a worksheet, rather than the value of the results

Insert, edit, delete, show, hide comments/notes

6. IMPROVE PRODUCTIVITY

6.1  CELLS APPOINTMENT

Appoint cell ranges, delete names for cell ranges

Use named cell ranges in functions

6.2  PASTE SPECIAL

Use the paste special options: add, subtract, multiply, divide

Use the Paste special options: values/numbers, transpose

6.3  TEMPLATES

Make a workbook based on an existing template

Modify template

6.4  CONNECT, IMPORT AND EXPORT

Edit, insert, remove a hyperlink

Link data in the workbook, between spreadsheets, between applications

Import delimited data from a text file

6.5  AUTOMATION

Create macros such as change page setup, apply a custom number format, apply the automatic formatting to a cell range, insert fields in the header, footer worksheet

Run a macro

Assign a macro to a button in the toolbar

7. COLLABORATIVE EDITING

7.1  TRACKING AND REVIEWING

Turn off, track off changes. Tracking changes in a worksheet using the preview pane.

Accept, reject the changes to te worksheet

Compare and merge workbooks

7.2  PROTECTION

Add, remove password protection for workbooks: opening, modification

Protection, removal of the protection cell, worksheet with a password

Hide, unhide formulas

8. TERMS OF SERVICES

___________________________

RELATED PAGES:

Advanced spreadsheets – handbook s and quizzes: link

Module 4 – Spreadsheets – Microsoft Excel 2010: link

Module 4 – Spreadsheets – LibreOffice Calc: link

Advanced spreadsheets – LibreOffice Calc: link

Advanced spreadsheets: analysis and special tools – quiz

Advanced spreadsheets: usage of functions – quiz

Advanced spreadsheets: protection, sorting and queries – quiz

Advanced spreadsheets: naming cell ranges – advanced formatting – quiz

Advanced spreadsheets: handling data – connecting templates and charts – quiz

Advanced spreadsheets: editing data and display – quiz