Home > Excel Help Portal > Using Excel Formulas

<<  (Page 1)  Previous              Next  (Page 3)  >>

Using Excel Formulas

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to use Microsoft
Excel formulas.

51. Fill in the Empty Cells Here is a Deja posting with some better answers using copy/paste than the solution I provide... Keywords: Fonts, Errors, Formulas, VBA, Macro, Web, Comments

 
52. How to Use the LOOKUP Function This article describes how to use the LOOKUP function in a Microsoft Excel 2002 worksheet or in a Microsoft Office Excel 2003... KB#324986. Keywords: Arrays, Errors, LOOKUP, HLOOKUP, VLOOKUP

 
53. Macro to Extract Data from a Chart In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged... KB#300643. Keywords: Charts, SERIES, Symbol, Errors, Formulas, Links, VBA, Macro, Object, Error Message, Web

 
54. How to Automate Excel to Create a New Workbook from Delphi This article demonstrates how to create and manipulate an Excel workbook using Automation from... KB#231614. Keywords: Charts, Sales, Borders, Colors, Fonts, Format, Arrays, Formulas, Class, Object, Window, COLUMN, RAND

 
55. Function That Uses the ActiveCell Property Returns Incorrect Result When you enter a formula that calls a custom function, the formula may return an incorrect result when you recalculate the worksheet. The formula returns the correct result only when you select the cell that contains the formula and then... KB#213684. Keywords: Formulas, VBA, Object, Web

 
56. How to Use VLOOKUP or HLOOKUP to Find an Exact Match The VLOOKUP and HLOOKUP functions contain an argument called range_lookup that allows you to find an exact match to your lookup value without sorting the lookup table. NOTE : It is not necessary to sort the lookup table if you use the... KB#181213. Keywords: Sorting, Colors, Arrays, Errors, Formulas, VLOOKUP, HLOOKUP

 
57. Prevent duplicate entries How can I make sure that duplicate invoice numbers are not entered?. In Excel 97, you can use the new Data Validation feature to do this. In our example, the invoice numbers are being entered in column A. Here is how to set it up for a single cell:The next cell to be entered is A9. Click in cell... Keywords: Errors, Formulas, VBA, Web, Validation, VLOOKUP

 
58. Excel -- Data Validation -- AdvancedTechniques Tips Use Dynamic Lists Data Validation Dropdowns and Change Events Data Validation Dropdowns and Freeze Panes Make the Dropdown List Temporarily Wider Make the Dropdown List Appear Larger -- Zoom in when specific cell is selected -- Zoom in when specific cells are selected -- Zoom in when any cell... Keywords: Criteria, Download, Fonts, Errors, Formulas, VBA, Validation, Window, MATCH

 
59. Excel -- Data Validation -- Dependent Lists Create Dependent Lists You can limit the choices in a Data Validation list, by using named ranges and the INDIRECT function. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list. Create Named Lists Start by creating Named Lists, which will be the... Keywords: Criteria, Download, Formulas, Defined Name, Validation, INDIRECT, OFFSET

 
60. Current Data Region and related items This page will provide some macros to simulate manual use of such things as CTRL + arrow key, which moves to the edge of the current data region. manual procedure: Select cell C1 and place into it "=a1*b1", then since it only makes sense to want a product if there is something in column B, extend... Keywords: Formulas, Printing, VBA, Macro, Web, Comments, CELL

 
61. Clear Constants from Selected Cells You can combine the two statements into one statement. Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents Fixing those things our macro might look like the... Keywords: Fonts, Format, Errors, Formulas, VBA, Macro, User Defined Function, Toolbar, Web, Comments

 
62. Help using Excel - a VB module to generate HTML web pages Excel contains an option to save a worksheet as an HTML file. These generated pages are often unattractive to look at and contain vast amounts of unnecessary code - making the web page slow to load. If you have a minimal knowledge of HTML coding and can recognise the workings of a simple VB macro,... Keywords: Dates, Fonts, Format, Styles, Arrays, Formulas, Printing, VBA, Macro, Text Files, HTML, Web

 
63. How to perform a TwoDimensional Lookup in Excel 2000 This step-by-step article shows you how to use Microsoft Excel 2000 to produce two-dimensional lookups in a worksheet. A two-dimensional lookup is a method of finding data with two arguments. The two arguments in an Excel worksheet are typically a... KB#275170. Keywords: Format, Arrays, Formulas, Natural language, INDEX, MATCH

 
64. Using OFFSET to maintain formulas when inserting lines This page is simply an extended example of descriptive information associated with the INSRTROW macro. Example of an Excel Table The above data view might appear as follows in the formula view. By coding in this fashion the use of the INSRTROW macro greatly simplifies the insertion of new rows. Why... Keywords: Formulas, Macro, Web, Comments, OFFSET

 
65. How to Use Visual Basic Code to Total Rows and Columns in an Array In Microsoft Excel, you can use arrays to calculate and manipulate data in a worksheet. You can also use Microsoft Visual Basic for Applications to store the values from a range of cells in a Visual Basic array. The sample macro code in this... KB#213472. Keywords: Arrays, Formulas, VBA, Macro, Web

 
66. Custom Function to Turn Nonadjacent Cells into an Array In Microsoft Visual Basic for Applications, you can create a custom function that will turn a nonadjacent selection of cells into an array. This is useful with many of the built-in Microsoft Excel functions that require a single range or an array... KB#213403. Keywords: Arrays, Formulas, VBA, Web

 
67. You cannot change a link path when an Excel AddIn is not loaded When you try to find and to change the link source of a Microsoft Excel Add-In (*.xla) that is referenced by a workbook, and the existing link source contains a long path, you may receive the following error message: Formula is too... KB#817578. Keywords: Add-In, Errors, Formulas, Links, Error Message

 
68. How to Return Multiple Values from a Custom Function In Microsoft Excel, there are two different methods that you can use to return multiple values from a custom function: One method returns a variable-sized array, and the other method returns a fixed-size array. This article contains sample... KB#213484. Keywords: Arrays, Formulas, VBA, Web

 
69. Excel functions for investment appraisal NPV and IRR are statistical tools for appraising projects and assisting in investment decision making. They measure cash flows over the period of a project and take account of the 'time value of money'. Monies spent or received at different times can not be directly compared. In general it is... Keywords: Cash Flow, Sales, Formulas, Links, NPV, IRR

 
70. How to rank Duplicate Values Sequentially in Excel 2000 This step-by-step article describes a formula that you can use to assign a unique rank for all numbers in a range, even if the range includes duplicate values. If a row or column of cells in a Microsoft Excel worksheet contains duplicate values,... KB#213916. Keywords: Rank, Arrays, Formulas, OFFSET

 
71. How to control the startup message about updating linked workbooks in Excel 2003 This step-by-step article describes how to control the startup message about updating linked workbooks in Microsoft Office Excel 2003. When you open a workbook that contains links to cells in other workbooks, you may receive the following... KB#826921. Keywords: Links, Startup

 
72. Relative and Absolute Formulas Merwyn from England sent in this problem. Is there a single formula in cell B2 which can be copied across and down to create a multiplication table? =VLOOKUP(A4,$A$2:$A3,1,FALSE) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or... Keywords: Formulas, VBA, Web, VLOOKUP

 
73. Using Calculate Method May Not Calculate Certain Formulas When you calculate values on a worksheet, formulas that refer to cells that contain custom functions may not be calculated... KB#144508. Keywords: Formulas, VBA, Macro, Web

 
74. Saveas, Save each worksheet as a separate workbook As a further aid Sheet1 will be deleted from the new workbook and if an existing sheet {sheet1, sheet2, sheet3} that remains is named the same as the worksheet name then it also will be deleted before copying in the original sheet to the beginning of the tab... Keywords: Format, Errors, Formulas, VBA, Macro, User Defined Function, VBE, Files, Hyperlinks, Web, Comments

 
75. How to Use the FREQUENCY Function The FREQUENCY function returns a frequency distribution as a vertical array. For a given set of values and a given set of bins (or intervals), a frequency distribution counts the number of values that occur in each interval. The FREQUENCY... KB#100122. Keywords: Arrays, Formulas, FREQUENCY

 
76. How to Return the Address of the Cell with the Largest or Smallest Value This article demonstrates two ways that you can find the address of the cell containing the largest or smallest value in a range of cells in a Microsoft Excel worksheet. If you are using a single row or column of data, you can use a worksheet... KB#213375. Keywords: Formulas, VBA, Web, CELL, OFFSET, MATCH, MAX, MIN

 
77. Excel Automation Fails Second Time Code Runs While running code that uses Automation to control Microsoft Excel, one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error: Run-time error '1004': Method 'name of... KB#178510. Keywords: OLE, Errors, VBA, Object, Window

 
78. Visual Basic Macro Examples for Working with Arrays This article contains sample Microsoft Visual Basic for Applications procedures that you can use to work with several types of... KB#291069. Keywords: Arrays, VBA, Macro, Web, Comments

 
79. How to use New "Find and Replace" Dialog Box in Excel 2002 This step-by-step article tells you how to use the Find and Replace dialog box in Microsoft Excel 2002. In Excel 2002, the separate Find and Replace dialog boxes are replaced by a single Find and Replace dialog box with new search and replace... KB#288291. Keywords: Colors, Format, Errors, Macro

 
80. Lots 'o Little Code I have been adding a lot of whole programs so I thought this was a good time to drop back to basics and show how I do some little stuff. This is a start, more to come shortly. So you have a password on some cells. You need to sort or something in your code but your users should not be able to move... Keywords: Sorting, Colors, Arrays, Errors, Formulas, Password, VBA, Macro, File Name, Scenarios, AVERAGE, INDIRECT

 
81. How to Use the Registry to Override the Default Behavior When Excel Tries to Recover Data Excel 2003 contains a feature that allows you to recover data from corrupted workbooks. This article describes how to use the registry to override the default behavior when Excel tries to recover... KB#826836. Keywords: Formulas, Object, VBA, Corrupted, Registry

 
82. How to Use a Custom Function in Another Workbook In Microsoft Excel, you can create custom Microsoft Visual Basic for Applications functions to perform calculations. By default, these custom functions are available to all worksheets in the workbook that contains the custom function. Therefore,... KB#213645. Keywords: Errors, Formulas, VBA, Web, Window

 
83. How to Rank Duplicate Values Sequentially If a row or column of cells contains duplicate values, the RANK function assigns the same rank value to every occurrence of the duplicate value. This article describes a formula that you can use to assign a unique rank for all numbers in a range,... KB#152567. Keywords: Rank, Arrays, Formulas, OFFSET

 
84. How to correct rounding errors in floatingpoint arithmetic Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation 1*(.5-.4-.1) may be evaluated to the... KB#214118. Keywords: Format, Errors, Formulas, ROUND

 
85. Performing a Lookup with Unsorted Data in Excel In Microsoft Excel, the LOOKUP worksheet function has a vector form and an array form. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row... KB#181212. Keywords: Sorting, Arrays, Formulas, LOOKUP, VLOOKUP, INDEX, MATCH, OFFSET, HLOOKUP

 
86. Excel RTD Function Cannot Return an Array Microsoft Excel 2002 and Microsoft Office Excel 2003 provide a new worksheet function, RTD, that allows you to call a Component Object Model (COM) Automation server for the purpose of retrieving data in real time. The RTD function cannot return... KB#286258. Keywords: Dates, Format, Arrays, Formulas, Class, VBA, Object, Parse, RTD

 
87. Charting Data From Different Sheets A more detailed description of a chart and its series formula is presented in the The Chart Series Formula page elsewhere on this site, but a brief description here is in order. Every chart series has a formula which describes the data in the series. For example, a simple series formula looks like... Keywords: Charts, Plot, SERIES, Statistics, Format, Arrays, Formulas, Links, INDIRECT

 
88. How to check and Manage the Status of Links in Excel This step-by-step article describes how to check the status of links and determine what actions are needed based on the status reported. The Edit Links dialog box in Microsoft Excel provides improved functionality, which allows you more control... KB#288940. Keywords: Format, Errors, Links, Object

 
89. Sample Visual Basic Macros for Working with Arrays This article contains sample Microsoft Visual Basic for Applications procedures that you can use to work with several types of... KB#213798. Keywords: Arrays, VBA, Macro, Web

 
90. Error When You Concatenate String and Numeric Values When you concatenate (join) several pieces of information together in a Microsoft Excel macro, the following error message may appear: Run-time Error '13': Type... KB#154873. Keywords: Errors, Formulas, VBA, Macro, Error Message, Toolbar, Web

 
91. How to Determine Top/Bottom Used Cells in a Sparse Array This article discusses how you can determine the beginning and end used rows of a range by using the IF, ISBLANK, MAX, MIN, and ROW functions. The results can provide information to other functions and can help you to determine variable ranges.... KB#142526. Keywords: Arrays, Errors, Formulas, ROW, MIN, MAX

 
92. Microsoft Excel 2000 Specifications This article explains the following Microsoft Excel 2000 specifications: Worksheet and workbook specifications Calculation specifications PivotTable report specifications This information is also covered in detail in Microsoft Excel Help.... KB#264626. Keywords: Solver, Sorting, Colors, Custom Number Formats, Styles, Arrays, Formulas, PivotTables, Toolbar, Scenarios, Column Width, Window

 
93. How to Solve for New XValues Using INDEX and LINEST This article contains information about how to determine values for a set of unknown x-values when you are given a set of known x-values and known... KB#147266. Keywords: Format, Arrays, Formulas, INDEX, LINEST

 
94. Help using the Excel SUMPRODUCT() function ). It can contain up to 30 different arrays. The following example shows a simple model to calculate the cost of four different clinical procedures. Six... Keywords: SERIES, Criteria, Days, Format, Arrays, Formulas, COUNT

 
95. How to Create and Use TwoInput Data Tables in Microsoft Excel This article describes how to create and use two-input tables, which allow you to test how changes in two variables affect one... KB#282856. Keywords: Format, Formulas

 
96. Creating a Megaformula This tip describes how to create what I call a "megaformula" -- a single formula that does the work of several intermediate formulas. An Example The goal is to create a formula that returns the string of characters following the final occurrence of a specified character. For example, consider the... Keywords: Charts, Format, Formulas, Printing, SUBSTITUTE, FIND, RIGHT

 
97. How to use the Paste Options Buttons in Excel This step-by-step article describes the functionality of the Paste Options button in Microsoft Excel. The Paste Options button becomes available when you paste data into cells in Excel. The button appears just below the pasted selection. When... KB#291358. Keywords: AutoCorrect, Dates, Format, Errors, Formulas, Links, Smart Tag

 
98. How to view the Error Signature from Office Application Error Reporting If Program Quits Unexpectedly If a Microsoft Office XP program experiences a serious error and quits, your computer records an error signature that describes the error that occurred. This article explains how to view these error signatures. The error signature... KB#289508. Keywords: Errors

 
99. Visual Basic Macros That Add or Remove Hidden Apostrophes You can use a Microsoft Visual Basic for Applications macro to change a formula or a value to text and vice-versa. To change a formula or value to text, run a macro that adds a hidden leading apostrophe in front of the formula or value. To restore... KB#213440. Keywords: Format, Formulas, Printing, VBA, Macro, Web

 
100. Visual Basic Macro to Change Between Relative and Absolute References In Microsoft Excel, you can use the ConvertFormula method in a Microsoft Visual Basic for Applications procedure to convert cell references from A1 reference style to R1C1 reference style. This method also allows you to change from an absolute to... KB#213465. Keywords: Styles, Errors, Formulas, VBA, Macro, Error Message, Web

 

<< (Page 1)  Previous              Next  (Page 3) >>