Excel Functions: Contents

Functions and Calculations

Sum, Average and Count - the most basic functions

Financial functions such as compound interest and loan repayments

A detailed example of calculating loan repayments

Investment appraisal using NPV and IRR

Weighted averages and assessing risk

Frequency an array function to count ranges of values

Statistics 1 - Averages, standard deviation and confidence

Statistics 2 - Quartiles and interquartile range

IF, conditions and selecting

DSum, DAverage - database functions

VLookup, Match to find a particular value within a range of data

If, And, Or, Rank, Errors - logical functions and preventing errors

A macro and an Advanced Filter to select records

Selecting values from drop down lists and boxes

Conditional SUM formulae and a costing model using SumProduct

Using Arrays to apply selective rules to functions

Formatting Cells and Values

Text, Abs and formatting cells with numbers or dates

Conditional formatting of cells (2 pages)

A VB function to return cell colors and sum the values of cells which are a particular color

Protecting cells and worksheets

Displaying number values as words

Extracting text from strings and phrases

Configuring the appearance of Excel at startup

A VBA function to display cell contents

Dates and Times

Date and Time functions and calculations

Calculating hours worked and shift enhancements

An office holiday leave planner (SumProduct + Conditional Formatting)

Calculating the difference between two Dates

Sharing data with other programs

A simple macro to create web pages from Excel

A more complicated macro to make formatted web pages from Excel

Creating XML data files from Excel and Access using VBA

Linking tables and charts in a Word document to an Excel spreadsheet

Using an Excel table as a data source for a Word Mail Merge *New*

Using PowerPoint to display information from Excel (2 pages)

Basic examples of displaying XML / XSL files in a web browser

An example of a cascading style sheet for web page design

Linking Access database data to an Excel worksheet *New*

Using a macro to export text files from tables and forms

Reference, Input and Navigation

Graphs which grow dynamically to include new data

Hyperlink navigation and buttons on worksheets

Two forms to input data and add it to a list using VBA macros

Using Indirect to enter cell references as text within formulas

A VB function to return worksheet names *New*

Using Offset and Choose to select particular cells or values

Auditing and other stuff

Audit tools for checking a spreadsheet

Testing and mapping spreadsheets to check for design errors

Adding a macro to a toolbar and keyboard shortcuts

Improving your graphs using pictures and better axis labels

Tips for accountants on using Excel