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 sums - a choice of functions and criteria

Conditional SUM formulas and a costing model using SumProduct

Using Arrays and SumIf to apply selective rules to functions

Formatting Cells and Values

Text, Abs and formatting cells with numbers or dates

Conditional formatting of cells and data bars (3 pages)

A VBA 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

A VBA macro to Divide a text string without splitting words

Configuring the appearance of Excel at startup

A VBA function to display cell contents

A VBA macro to convert text cell values into numbers

Dates and Times

Date and Time functions and calculations

Payroll calculations - enhanced rates for unsocial hours * New *

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

Publishing a spreadsheet as a PDF file * New *

Embed spreadsheets within web pages or blogs (Excel Web App) * New *

Creating XML data files from Excel and Access using VBA

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

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

Using PowerPoint to display information from Excel

Linking Access database data to an Excel worksheet

Using a macro to export text files from tables and forms

Including variables within an HTML web page using Javascript

Reference, Input and Navigation

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

Using Offset and Choose to select particular cells or values

Auditing, charts 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

Drawing organisation charts and automating their creation with a macro

Improving your graphs using pictures and better axis labels

Graphs which grow dynamically to include new data

Create a waterfall chart in Excel

Tips for accountants on using Excel

Download this PDF file onto your PC
and keep it as a handy reference