Convert Excel Spreadsheets Into Live Calculating Web Pages
Summing Formulas
Excel SUM Formula Probably, the most widely used Excel formula, the SUM function in Excel is specifically designed to add values from different ranges, or one range. The SUM formula can be typed into a cell in Excel, or inserted via the Insert Function tool to the left of your Formula bar.
Excel Autosum Function Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.
Array Formulas in Excel I strongly suggest you read this very important information on using array formulas in your spreadsheets. Array formulas can let you specify more then one criteria to Sum, Average, Count etc by. Many examples of how to use them.
Excel Conditional Sum Wizard The Conditional Sum Wizard is an Add-In to Excel that is used to summarize values in a list based on set criteria.
Sum With Multiple Criteria Examples of Excel formulas to sum a range of cells that meet multiple criteria. ,DSUM, SUMPRODUCT and SUM with an IF function/formula.
Sum Every nth Cell
Sum nth Largest/Smallest
Sum Cells by Color
Increase/Decrease Values If you have values on an Excel Worksheet that you need to permanently increase, or decrease you can use Paste Special. No Excel formulas needed!
Excel Subtotals In Excel we can use the Subtotals feature found under Data on the Worksheet Menu Bar to Subtotal a table of data.
Bold Excel Subtotals Here is how we can use Conditional Formatting in Excel to automatically bold the results of Subtotals.
Making the SUBTOTAL Function Dynamic How to use the one function (not feature as above) to perform a chosen operation on only visible cells after using Auto Filter.
Excel Data Tables Data Tables are a range of cells that are used for testing and analyzing outcomes on a large scale.
Sum Between Date Ranges
counting Formulas.
Do this often? Free Custom Excel Functions Add-in Count With Multiple Criteria Examples of Excel formulas to count a range of cells that meet multiple criteria. ,DCOUNT, DCOUNTA, and SUM with an IF function/formula.
Count Each Item Only Once Count repeats in range one-time only.
Count Words in a Cell Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. These Excel formulas show you how.
Count of Each Item in a List With the aid of Advanced Filter and the COUNTIF we can get a count of each item that appears in a list.
Count Between Date Ranges
LOOKUP & REFERENCE Formulas.
Do this often? Free Custom Excel Functions Add-in VLOOKUP Perhaps one of Excels most commonly used Excel Formulas is the VLOOKUP. It is also possibly the Excel formula that most people have problems understanding.
Stop #N/A! In Vlookup
Excel HLOOKUP Another one of Excels most commonly needed Excel Formulas is the HLOOKUP.
Left Lookup in Excel Excel is very rich in Lookup formulas, with perhaps the VLOOKUP being the most popular. However, the draw-back with all Excel's Lookup formulas is that they will only look in the left most column and return the result from the corresponding cell to the right. There are times when users need to lookup data in any column of table and return the corresponding cell to the left.
Excel Lookups With Array Constants Would like to show you what I call: In-Cell-Lookups. These are the perfect replacement for multiple nested IF functions.
Dynamic Formulas Rather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use the INDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right. However, the the same principles can apply to most Excel formulas.
INDEX/MATCH Functions While the Vlookup Function is very useful, it cannot look in anyonly the 1st. Also, it cannot offset x columns to the left or return the value x rows before or after the found value. An INDEX & MATCH combo will allow for all of this flexibility. Column,
Find Nth Occurrence in Excel As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value.
Excel Lookup Table VLookup is the perfect Excel formula for numerical values contained in a range. However if you tried to use VLookup with text in a table, it's use would be limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems.
Dynamic Excel Lookups These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table.
Lookup & Return Corresponding Result While we can use any of the links above for lookup formulas, all require a table of cells in a Worksheet. If you only have small number of items to return based on the value of another cell, we can do the lookup without leaving the cell!
Excel Hyperlinks How to get the most from hyperlinks. Hyperlinks that still work when a Worksheet name changes. Create a hyperlink to a Chart Sheet
Hyperlink to Lookup Result Excel is quite rich in Lookup type formulas, some of the more popular ones are VLOOKUP
, INDEX/MATCH and HLOOKUP . These all do a great job in looking up a value we specify and then return a corresponding result. However, it's often the case that we need to go to the row containing the found value, or its offset return.
Dynamic Reports Here you can see how we can the Database functions to produce dynamic reporting from a table of data.
Multi-Table Lookup Use the Dependent Validation Lists method to tell Excel to lookup any chosen item in any table you tell it.
IF Formula. Do this often? Free Custom Excel Functions Add-in
Nested IF Formula Limitation One limitation of Excel is that we can only nest Excel formulas up to 7 levels. This is particularly limiting when trying to add nested IF Functions/Formulas that require greater than 7 conditions.
7 Nested IF Formula Limitation Workaround 2
ROUNDING & PERCENTAGES. Do this often? Free Custom Excel Functions Add-in
Round Excel Numbers Methods and Excel Formulas for rounding numbers in Excel
Percentages in Excel A percent is a ratio of a number to 100 and is usually expressed using the percent (%) symbol. In Excel, a number can be expressed in a few different ways and used to calculate a percentage in Excel.
Excluding ZEROS. Do this often? Free Custom Excel Functions Add-in
Average Excluding Zeros Excel has a built in formula/function that makes averaging a range of cells easy. However, the Excel formula AVERAGE does not exclude zeros.
Minimum Excluding Zeros Excel has a built in formula/function that makes finding the minimum value in a range of cells easy. However, the Excel formula MIN does not exclude zeros.
Tips and tricks
Quick & Dirty Tips For Microsoft Excel
Efficient Excel Spreadsheet Design This page is a must read for anyone with the task of designing and setting up an Excel spreadsheet. It talks about formatting, layout and calculation speed.
Excel Tips and Tricks This page consists of many tips and tricks you can use in Excel, from shortcut keys to working with formulas and functions. If you know of a good one, please let us know!
Handy Hints For Spreadsheets Here you will find out how to use Excels Conditional Formatting and Data Validation. It explains in detail how you can use these great Excel features to work with duplications in your spreadsheets. Best of all it shows how they can be used to prevent them!
Excel Formulas This page will take you to lots of examples of Excels Formulas. It includes formulas for dates, times, text and numbers. Each page also includes some formula tips and tricks.
Nifty Excel Formulas Download a Workbook full of nifty Excel formulas: For Last Value, Close Match, AutoFilter Lookup & Much More
Excel resources
SELF HELP
FREE Excel Help Forum (The place to get your questions answered). Get your questions answered for free, often within minutes! The most popular area of our site. Give yourself and/or your company totally free 24/7 support. You can even upload/download example files
Excel Formula Errors As soon as you have discovered how to use formulas in Excel, you will likely need to lean how to correct or hide formula errors.
Excel Newsletter Archive Have a browse through our free Excel newsletter archives. Here you will find a bit of everything from basic shortcuts to VBA for Excel. If you like what you see then sign up, it's completely free!
DEBUG / EVALUATE FORMULA ERRORS #NULL!, #DIV/0!, #VALUE!,#REF!,#NAME?, #NUM! and #N/A
FREE Excel Training Free Level 1 (Basic) Excel Training Compliments of Ozgrid!
FREE Excel Downloads All Excel downloads below here are free.
Excel Forum FAQ List of the most popular asked questions in the Excel help forum . Excel Formulas through to Excel VBA macro code.
Excel Frequently Asked Questions and Answers . An ever growing list of popular questions and answers.
See also: Excel resources
Charts
Excel Charts Here are many examples that can be used to create Excel charts with a difference. The Excel charting pages listed here show many different charting techniques, examples and solutions.
Excel Chart Tips and Tricks There are many little known tricks that can be used with Excel charts and these are just some of them. If you want your chart to update automatically, not plot zeros, or have scrolling charts etc, then you will find it here!
See also: Excel resources
commission/tax formulas. Do this often? Free Custom Excel Functions Add-in
Calculate Sliding Scale Tax Calculating tax, or commission that is based on a sliding scale, or by bracket, can be complicated. The Excel formula here is one that can be used using Excel's built in function/formulas. That is, the IF function/formula and the SUM function/formula.
Calculate Fixed Rate Tax The Excel formula VLookup can be used to look for numeric values in lists and tables and to use those values in a calculation. A perfect example of how VLookup can be used to glean information is to work out how much tax you are required to pay.
PIVOT TABLES
Excel Pivot Tables Take the FREE online tutorial on Excel Pivot Tables. Download the Excel Workbook examples are more.
Pivot Table Tutorial
Pivot Table Grouping Problems
Calculated Fields in Pivot Tables Add your own custom calculations in Pivot Tables.
Grouping Pivot Table Data Grouping Pivot Table Data. How to Group so you don't get: Cannot Group that Selection
See also: Excel resources
DATES & TIMES
Excel Date and Times This page is a must if you are wanting to work with dates and times in an Excel spreadsheet. It explains how Excel stores dates and times, Excel formulas for time & dates, give the magic numbers needed for dates and times and date and time conversions and functions.
Mask Time Entries in Excel Unlike Access, Excel does not allow for us to mask time and/or date entries. However, we can use the Worksheet_Change Event to achieve masked time entries
Mask Date Entries in Excel
Return Weekday of Excel Dates
Return Weekday Day of Week To return the weekday of any date in Excel we can use any of the Excel formulas, or methods shown on this page.
Excel Calendar for Valid Dates Easy to follow steps on how to create a user friendly Excel Calendar.
Convert Dates How to convert those imported dates.
Calculate a Persons Age in Excel We can use the Excel formulas shown here to return the age of any person in years, months and days. It makes use of the little known DATEDIF Excel formula/Function
See also: Excel resources
NAMED RANGES
Excel Named Ranges/Using Names in Excel
Sheet Level Named Ranges Normally, when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!
Named Constants Let's look at Named Constants. These are simply values that are given a meaningful name.
Named Formulas Now we have seen how easy Named Ranges and Named Constants are, we can look at a little known ability of Names where they can used to create custom formulas
Relative Named Ranges A relative named range is a named range that refers to a range that is relative to the position of the cell pointer at the time they are created. This makes them perfect for formulas that are required to be moved around a worksheet and are easy to create.
Dynamic Named Ranges Possibly one of Excels most underutilized aspects is its ability to create dynamic named ranges that will expand and contract according to the data in them.
Advanced Dynamic Named Ranges They are ideal to be used in a vast array of different situations. I thought I would show you some Dynamic Named Ranges with a twist.
VIEWING & Printing
View Multiple Sheets of an Excel Workbook A very handy feature of Excel is its ability to allow you to create a copy of a workbook and compare and work with the copy whilst simultaneously working with and viewing the original workbook. There are two ways in Excel that you can do this.
View Multiple Different Workbooks To allow you more flexibility when working with workbooks, Excel allows you to view workbooks in different windows by offering four different custom views to work with.
Save as Workspace If you wish to compare either multiple workbooks, or the same workbook over and over again,and you have set up a custom view to do this, Excel has the facility to save a custom view as what is known as a workspace
Excel Freeze Panes When you have a large worksheet, it is often required that column or row labels remain in view. By using Excel's Freeze Panes feature you can freeze either columns or rows in your worksheet so that they remain visible whilst you scroll.
Excel Split Panes A very handy feature of Excel is its ability to allow you view more than one copy of your worksheet, and for you to be able to scroll through each pane of your worksheet independently.
Limit/Restrict Scroll Area on an Excel Worksheet With Excel having 256 Columns and 65536 rows you may wish to limit, or restrict, the area that users can scroll to on your Worksheet.
Printing in Excel Basics of printing in Excel along with some tips and tricks.
Efficient Excel Printing The content of a Worksheet is far more important than using a great deal of formatting to pretty it up (although there are some basic fundamentals for efficient Excel Spreadsheet Design
). Headers & Footers in Excel As the names suggest, a Header is something that appears at the top of every page, and a Footer is something that appears at the bottom of the page.
TEXT
Pull Last Word(s) After Last or First Instance of a Specified Character Suppose you have the text "11003-blue-40 lbs" in Cell A1 and you need to get the last words/characters after the last occurrence of the dash (-), i.e. "40 lbs", you can use......
Extracting Words From Text in Excel Excel has very powerful and useful Text formula/functions that we can use to extract words from a string of words. Or, put another way, parse out specific words from text. The examples here show how we can use the Excel formulas to do this.
Convert Text to Upper/Proper Case Excel has 2 built in Excel Formulas for converting text to either UPPER CASE or Proper Case. However, there are many instances when using the Worksheet Function approach is not practical.
Converting Text Numbers to Real Numbers Sometimes Excel sees numbers as text and this causes problems. Here are two simple ways that should help both constants and formulas.
Sort Alphanumeric Text Excel has a problem trying to sort alphanumeric cells in cells by the number portion only. The reason is simply because Excels Sort features evaluates each cell value by reading left to right. However, we can over-come this in a few ways with the aid of Excel Macros
FILTERS
Excel AutoFilters One of the most useful functions in Excel is the AutoFilter. The AutoFilter allows a user to filter items in a list according to a set criteria. You can filter text, numbers or dates with AutoFilter.
Display AutoFilter Criteria Excel's AutoFilter is one of Excel's most useful features. However, the one small draw-back is it's hard top tell the criteria being used at a glance.
Excel Advanced Filter Other than Excel's very popular Auto Filter, we have at our disposal Excel's Advanced Filter. In a nutshell, Excel's Advanced Filter can be used to filter by more than 2 criterion, where Auto Filter has a max of 2! It also has a very nifty feature that allows us to create a list of unique items from a list with repeated data. In addition, we can even use Excel formulas for Excel's Advanced Filter criteria.
Goal Seek Goal Seek can be used when you know the result of a formula, but not the input value required by the formula to decide the result, reverse calculation.
CONDITIONAL FORMATTING
Conditional Formatting In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).
Conditional Formatting 3 Criteria Limit If you are familiar with Conditional Formatting in Excel you will know it has a 3 criteria limit. However, with the aid of Excel Custom Formats we can have up to 6 Conditional Formats for Font color. If you need background color and/or you need text values, see Conditional Formatting 3 criteria Limit for a Excel VBA method.
Alternate Row Coloring Now that Excel has Conditional Formatting (since Excel 97) we can use it to create an alternate row color for a table of data. This is often referred to as color banding and means that every second row should be filled with a specified color.
Bold Excel Subtotals Here is how we can use Conditional Formatting in Excel to automatically bold the results of Subtotals.
Formats
Custom Formats in Excel Excel has custom number formats that can be used on dates, times, text and currencies. There are some practical examples and a link to Microsoft’s information on custom number formats that I highly recommend.
Custom Formats With Symbols Format Cell To Include Symbols. Superscript & Subscript in Custom Formats
CONSOLIDATION & OUTLINING
Excel Consolidate Consolidation is the process of combining values from several ranges of data either from within the same or different workbooks. It can be used to summarize data from different worksheets into master worksheet and create a report using a variety of calculations.
Excel Auto Outline Outlining is a means of viewing levels of detail as required by collapsing or expanding to hide or show information. You can identify subtotals and hide or collapse detail so that only subtotals appear on the screen.
Enable Outline/Outlining on a Protected Worksheet In Excel 2000 Microsoft added many new levels of Worksheet protection to Excel. Unfortunately they have neglected to add one that would allow Excel users to use Grouping/Outlining on a protected Worksheet.
Excel Scenarios Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.
TRACK CHANGES
Excel Track Changes The Track Changes feature in Excel is used to record a history of any changes made to the workbook. You can log user names, the type of change, the date the change was made and where about in the workbook the change occurred.
VALIDATION
Data Validation In Excel 97 Microsoft introduced a cool new feature called Data Validation. It can be found under Data on the Worksheet Menu Bar. Its purpose is to help stop entries into cells that are not within the criteria we set.
Dependent Validation Lists A common question from Excel users is along the lines of; I have a list in a cell that has used Data>Validation with the "List" option. I would like to have another cell display a list that is depended on the item chosen in my first list. Uses Excel formulas!
Decreasing Validation Lists If you need to have a list of options that decreases as users select from it, you can use Data Validation and some Excel VBA Code .
Auto Complete Validation List What would be really nice is if Excel would AutoComplete based on our list! Unfortunately no such standard feature exists in Excel.
Stop/Prevent Blanks in Excel With the aid of Data Validation and some Excel formulas we can ensure a table, or list cannot have blank/missing entries.
Multi-Table Lookup Use the Dependent Validation Lists method to tell Excel to lookup any chosen item in any table you tell it.
Duplicates
Highlight Duplicates in Excel Example of how we can use Conditional Formatting to highlight duplicates in Excel.
Stop/Prevent Duplicates in Excel If you are familiar with the Excel Data Validation feature, we can move into a great use for it! That is, prevent duplicates in an Excel Spreadsheet.
LINKS AND PROBLEMS
Update Links in Excel When you have formula links in an Excel Workbook (destination) to another Excel Workbook (source) you will be asked if you wish to update links in the Workbook (destination) each time you open it. This can be painful when you always want, or don't want, links updated.
Stop Do You Want To Save Changes Some users will get the "Do You Want To Save Changes Made to..." when closing an Excel Workbook that has had no changes, or even a new Workbook without changes.
Efficient Excel Spreadsheet Design This page is a must read for anyone with the task of designing and setting up an Excel spreadsheet. It talks about formatting, layout and calculation speed.
Workbook Size Blow-out and Phantom Links . Here are some step-by-step instructions that you can use to get your file size back to where it should be. There is also step-by-step instructions on how to get rid of those phantom links.
Start-up/Command-line Switches For Excel Fix, repair or customize how Microsoft Excel starts
other
Record Macros in Excel A macro is a series of key strokes or mouse actions that are recorded and saved as a program. You can activate your macro at any time you like to repeat your recorded steps. Macros can be assigned to a shortcut key, an object or even to your toolbar to make them easier to access.
Assign Macros in Excel Once you have recorded your Excel macro , there are a number of ways that you can run it.
Create Custom Toolbars The toolbars in Excel can be easily manipulated to allow a user to customize their own toolbar. This is great for situations where there is a shared workbook used by multiple users.
Create Excel Worksheet Templates Worksheet templates are used to store settings you want to appear on all new sheets of the same type. The default worksheet template is stored as sheet.xlt.
Create Excel Workbook Templates Workbook templates can contain things such as specific formatting styles, column/row labels, headers and footers, formulas and even macros. Workbook templates are used for settings you want in all new workbooks based on the template.
Return Excel Worksheet Name to a Cell In Excel it is possible to use the CELL function/formula and the MID, LEN and FIND to return the name of an Excel Worksheet in a Workbook. We can also use a handy Custom Excel VBA Function.
Convert Excel Formulas and Function to Values Only Here are a few ways that we can convert Excel formulas to their values.
Increment by Row When Copying Across Columns This easy to use method can save lots of time.
Controls from Forms Toolbar vs Controls from the Control Toolbox Toolbar Some useful information on these 2 types of Controls.
Variable Worksheet Names in Excel Formulas There is sometimes a need to have a Worksheet name in a cell as a variable and to use that Worksheet name in a formula. This then enables one to switch Worksheet names and have one single formula able to return results from all Worksheets.
Start-up/Command-line Switches For Excel Fix, repair or customize how Microsoft Excel starts
