 |
|
|
Page 2 of 2 Excel Formulas Page 1
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
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.
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
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 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.
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
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.
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.
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.
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.
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
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
Back to Excel Formulas Page 1
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel
| MSSQL Migration
Toolkit |
Monte Carlo Add-in |
Excel
Costing Templates