MeadInKent
| Links | Books | Alphabetical Index | Excel 2010 Screen Layout |

Excel Functions: Contents

These pages contain information and examples of Excel spreadsheet functions and topics such as graphs, testing and short cuts.


Maths

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


# #N/A & Abs Age And Array Audit Autosum Average bandings BestFit Book Buttons Cell Chart Choose 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 Formats Formatting Forms Formula Frequency FV Graphs Grouping Hyperlinks If Indirect Int Interest Investment IRR IsError Isna Large Listbox Lists Loans Logical Lookup Macro Mapping Match Max Mean Median Mid Min Mod Mode Normal Now NPV Numbers

Conditional

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

Format

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


Help and tips using Microsoft Excel spreadsheet functions
Colin discovers that wielding the sword of truth and shield of fair play can be difficult while wearing the helmet of hypocrisy


Support this site on Facebook

Custom internet search for Excel click here
(powered by Google)

Dates

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



For a detailed index click here

Sharing

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



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


Reference

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


Your Excel questions on my Discussion Board. Can you offer help and solutions to other users?


For a detailed index click here

Help moving to new version of Excel


Charts

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
Tips for accountants on using Excel


download this free reference page with examples of 8 Excel functions

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

The Excel Functions guide (available for sale) has been updated to Excel 2010


This site contains many useful examples of Excel functions and other information. Add it to your bookmarks or favorites now so that you can easily return here again.

Excel 2010 Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

View a selection of recommended books about Excel. These can be ordered from either Amazon.com or Amazon.co.uk.

Was this Excel information useful?

Do you have any comments or was there something else you were looking for?

Click here for Feedback form


feedback

Please note that I cannot reply if you don't tell me your email address!

If you are seeking help with an Excel
query, visit my discussion board



Office
Leave
Planner
Office Leave database

an Access database for
recording and displaying
planned staff absences

[View details]


file: excel.htm Open MeadInKent Facebook page page last updated Jan14 working to protect human rights worldwide