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

Performance issues with large spreadsheets

# #N/A & Abs Address Age And Array Audit Autosum Average bandings BestFit Book Bullet Points Buttons Cell Chart Choose Code ColorIndex Colours ColourScales Combo Comments Compound Concatenate Confidence Configure Count CountA Criteria CSV DataBars Date Datediff Daverage Defaults Dependents Design DropDown Dsum Errors Filename Filtering Financial Find Footers Formats Formatting Forms Formula Frequency FV Graphs Grouping Hyperlinks If Index Indirect Int Interest Investment IRR IsError Isna Large Listbox Lists Loans Logical Lookup Macro Mapping Match Max Mean Median Mid Min Mod Mode NamedRanges Normal Now NPV Numbers

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

Transpose a table and convert rows into columns

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

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

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

Linking survey results from a Google Docs Form into Excel

Creating basic XML data files from Excel and Access using VBA

Displaying external files in a web page using iFrames

Including variables within an HTML web page using Javascript

Including tables within an HTML web page using Javascript

Calculating HTML image maps for Excel charts

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

A presentation using only Excel with formatted headers and footers

Linking Access database data to an Excel worksheet

Using a macro to export text files from tables and forms

Offset Or Organisation Output Parsing PDF Personal PMT PowerPoint Precedents Principal Probability Projects Protection R1C1 Rand Rank Repayments Rept Ribbons Risk search Series sheet Shortcuts Slicers Small Statistics Styles Sum SumProduct Testing Text Time TrafficLights Transpose True Validation Value VBA VLookUp Waterfall WebApp Webpages Weekday Weighted Word Xlstart

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

Using Index to summarise Named ranges

Making use of R1C1 cell references

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

Link a chart axis min and max values to worksheet cells

Showing characters and codes on an embedded web page

Adding symbols and bullet points to text

Tips for accountants on using Excel

Excel 2010 screen layout and macro security

Making dashboards and pivot table slicers

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