Home > Excel Help Portal > Using Excel Formulas

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

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.

151. Help using Excel spreadsheet functions - vlookup, isna, match The Vlookup function is commonly used to get details from a table of data. A specified value is looked for in the first column and a value from an adjacent column is returned. =Vlookup(SearchItem, TableRange, ColumnNo, Exact). For example you may search for a surname in a list and then return a... Keywords: Errors, Formulas, VLOOKUP

 
152. How to Use the INDIRECT Function to Create References In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. You can use the INDIRECT worksheet function to create linked references to other workbooks. You can reference each... KB#213933. Keywords: Errors, Formulas, INDIRECT

 
153. How to Restrict Numeric Entry to Only One Cell in a Range This article shows you how to use data validation to ensure that a number can be entered into only one cell in a range that you... KB#188661. Keywords: Errors, Formulas, Error Message, Toolbar, Validation

 
154. Excel Frequency function Frequency is an unusual array function and it works differently to most other normal functions. It can not simply be typed into a cell or even entered properly using the Excel Function Wizard. Note that this function does not analyse values into categories e.g. household expenditure into groups... Keywords: SERIES, Arrays, Formulas

 
155. How to Use Data Validation to Prevent Duplicate Entries This article explains how to use data validation to prevent the creation of duplicate entries in a... KB#213185. Keywords: Styles, Errors, Formulas, VBA, Error Message, Validation

 
156. Days of the Week Before March 1, 1900 Are Incorrect When you use the WEEKDAY function to compute the day of the week for dates prior to March 1, 1900, the function returns an incorrect result. For example, if you use the WEEKDAY function to determine the day of the week for February 16, 1900,... KB#106339. Keywords: Dates, Errors, Formulas, WEEKDAY

 
157. Displays a Tooltip with Duplicate of Number in DDE Link Richard from the Computer America radio show sent in this interesting problem. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a... Keywords: DDE, Third-Party, Format, Formulas, Links, VBA, Web, Column Width, NOW

 
158. How to Use the INDEX Function to Find Data in a Table This article describes how to use the INDEX function to find data in a table at the intersection of a specific row and column. There are two forms of the INDEX function, Array and Reference . The primary differences between the two forms are... KB#324988. Keywords: Arrays, Formulas, INDEX

 
159. How to use Functions in Formulas in Excel 2000 This step-by-step article explains how to use functions in formulas in Excel 2000. A formula is an equation that performs operations on worksheet data. Formulas can perform mathematical operations, such as addition and multiplication, or they... KB#304423. Keywords: Formulas, Toolbar

 
160. How to force Excel to Always Reference the Same Cell in Excel 2000 This step-by-step article shows you how to use the INDIRECT function to continually reference the same cell in a formula in Microsoft Excel 2000. How to Use the Indirect Function To anchor a reference while you insert or delete rows, columns,... KB#214143. Keywords: Formulas, INDIRECT

 
161. How to Create a Conditional Format to Hide Errors In Microsoft Excel, you can create conditional formatting on a cell, or a range of cells, so that error values are not displayed in the... KB#182189. Keywords: Conditional Formating, Custom Number Formats, Errors, Formulas

 
162. Excel -- Combining Data Combining Data You can combine data from multiple cells into one cell, either "As is", or with additional text and formatting. Also, you can combine text, and the result of a function. Combine Text from Multiple Cells Add Spaces to Combined Text Combine Text and a Number Combine Text and a... Keywords: Format, Formulas, TEXT, TODAY

 
163. Custom Function Returns #VALUE! If Returning Array Larger Than 5,461 Elements When you calculate your workbook, some cells may contain a #VALUE!... KB#250828. Keywords: Arrays, Errors, Registry

 
164. An Overview of Data Tables in Microsoft Excel You can use data tables (on the Data menu, click Table ) to see how different input values affect the results of a formula. By doing so, you can test different input values for a formula, without having to retype or copy the formula for each... KB#282852. Keywords: Format, Formulas

 
165. InputBox and MsgBox Keywords: Arrays, Errors, Formulas, VBA, Object, Web, Comments

 
166. Excel Filters -- Advanced Filter -- Complex Criteria Complex Criteria This page gives details on criteria used for specialized Advanced Filters. For an introduction to Advanced Filters, click here. The samples on this page assume a data layout as shown at right. The database is in columns A:D and the criteria range starts in cell F1. Note: When you... Keywords: Advanced Filter, Criteria, Dates, Errors, Formulas, LARGE, FIND

 
167. #REF! Error Recalculating Worksheet with Defined Name When your worksheet is recalculated, some cells may display the #REF! error... KB#221058. Keywords: Errors, Formulas, Links, Defined Name, File Name

 
168. How to work with Serial Number Dates and Julian Dates in Microsoft Excel 2000 This article explains how to convert serial number dates to Julian dates and how to convert Julian dates to serial number dates in Microsoft Excel 2000. Julian dates refer to the number of days from the first of the year and the number of days... KB#214099. Keywords: Dates, Days, Formulas, Defined Name, TEXT, DATEVALUE

 
169. Using Data Validation to check for repeated values Sometimes you just don't want data to repeat itself. On an order form or an inventory sheet, for instance, you may not want a part number entered in one cell to repeat in another cell. You can use Excel's Data Validation feature to to prevent a value from appearing more than once in a range. In the... Keywords: Charts, Format, Errors, Formulas, Printing, Error Message, Validation, COUNTIF

 
170. The Excel DATEDIF function While it is easy to determine the number of days between any two dates simply by subtracting one from the other, it is extremely difficult to return a string that says something like '24 years, 6 months and 8 days'. The maths and logic involved in this apparently straightforward question are very... Keywords: Dates, Days, Months, Years, Formulas, Files, DATEDIF, NOW

 
171. How to preserve Formulas If You Save a Document As a Text File in Excel 2000 In Microsoft Excel, you may occasionally want to save a document as a text file; for example, to fix corruption in the file. Although an Excel file is saved as a text file, only the active sheet and the displayed values are saved. All formulas on... KB#213913. Keywords: Formulas, Text Files

 
172. How to Change the Case of Text This article describes how to use worksheet functions to change the case of... KB#263580. Keywords: Formulas, LOWER

 
173. Excel -- Pivot Tables -- GetPivotData GetPivotData Generate GetPivotData In Excel 2002, and later versions, when you type an equal sign, then click on a cell in a pivot table, you may see a GETPIVOTDATA formula, instead of a cell reference. If you prefer to use a cell reference, you can: or To add a Generate GetPivotData button: Click... Keywords: Download, Errors, Formulas, PivotTables, Toolbar, GETPIVOTDATA

 
174. "Do You Want to Save the Changes You Made to { ?" in French Excel 2000 When you close a workbook in the French version of Excel 2000, you receive the following message: Do you want to save the changes you made to { ? Microsoft Excel recalculates formulas when opening files last saved by an earlier version of Excel. The... KB#269679. Keywords: Formulas, Files

 
175. Names Keywords: Formulas, Web, Comments

 
176. An Excel VBA macro and button to select another workbook This page illustrates a simple macro that can be placed in your Personal.xls workbook and is then available to switch between all open workbooks in turn. This is an alternative to selecting the Window menu option (or using a keyboard shortcut) and then choosing the required workbook. Excel allows... Keywords: Formulas, VBA, Macro, Toolbar, Window, MATCH

 
177. How to suppress #DIV/0! Error Value in Division in Excel 2000 When you divide by 0 (zero) or a blank cell, Microsoft Excel displays the error value #DIV/0! as the result of the calculation. This article shows you how to use the IF worksheet function to suppress the #DIV/0! error value. Suppress #DIV/0... KB#182188. Keywords: Conditional Formating, Errors, Formulas

 
178. How to use the Error Checking Options Button in Excel 2002 This article explains the functionality of the Error Checking Options button in Microsoft Excel. Error Checking Options The Error Checking Options button is displayed when the formula in an Excel worksheet cell triggers an error. In addition to... KB#291361. Keywords: AutoCorrect, Errors, Formulas, Formula Auditing, Smart Tag

 
179. Color, Sorting on Color What is meant by color? There is Color Index that is used by Excel, there is RGB. How are the cells colored, there is normal cell formatting and there is Conditional Formatting. Once you have a number it's not likely you could use it directly for the ordering you want. Finally obtaining normal cell... Keywords: Sorting, Colors, ColorIndex, Format, Formulas, VBA, User Defined Function, Web, Comments

 
180. Help using Excel database functions These are more complex versions of the basic functions Sum, Average and Count. They are part of a series known as database functions and they are used to interrogate lists of data held on a worksheet. For the selected table of data these functions will perform their processing only on records (or... Keywords: SERIES, Criteria, Rank, Arrays, Formulas, Links, Hyperlinks, DAVERAGE, DCOUNT

 
181. Custom Function to Average a Range Without Highest and Lowest Values For statistical purposes, you may want to calculate the average of a data sample without its highest and lowest values. In Microsoft Excel, you can write a Visual Basic for Applications (VBA) macro to do this, based on the following... KB#213495. Keywords: Arrays, Formulas, VBA, Macro, Web

 
182. Move, Copy, Replace, Populate ThisWorkbook.VBProject.VBComponents("Module1").Export "Test.bas" Workbooks("Book3.xls").VBProject.VBComponents.Import "Test.bas" Kill "Test.bas" ActiveSheet.Copy After:=Sheets(Sheets.Count) 213548 - XL2000: "255 Characters in Cell" Error When Copying Worksheet Compare to macro I created below,... Keywords: Errors, Formulas, Clipboard, VBA, Macro, Web, Comments, LEN

 
183. How to sort Cells Without Sorting Linked Cells in Excel 2000 This step-by-step article shows you how to sort linked data without affecting cells that are linked to the original data. If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated... KB#212079. Keywords: Sorting, Formulas, Links, OFFSET

 
184. How to Force MS Excel to Always Reference the Same Cell To continually reference the same cell in a formula in Microsoft Excel, use the INDIRECT... KB#51918. Keywords: Formulas, INDIRECT

 
185. Excel -- Data Validation -- Use List from Another Workbook Use a List from Another Workbook Create the Source List Create a Reference to the Source List Create the Dropdown List Create a Dynamic Range from Another Workbook You can use a list from another workbook as the source for a Data Validation dropdown list. For data validation to work, the workbook... Keywords: Criteria, Errors, Formulas, Validation, OFFSET

 
186. Excel -- Data Entry Data Entry Tips Enter Data Quickly -- Keyboard Enter Data Quickly -- Mouse Enter Data in a List Enter the current Date Enter the current Time Copy Value from cell above Copy Formula (exact) from cell above Copy Formula (relational reference) from cell above Ctrl + Enter Use Excel's built-in Data... Keywords: Text Box, Formulas

 
187. How to Suppress #DIV/0! Error Value in Division When you divide by 0 (zero) or a blank cell, Microsoft Excel displays the error value #DIV/0! as the result of the calculation. This article shows you how to use the IF worksheet function to suppress the #DIV/0! error... KB#291050. Keywords: Conditional Formating, Errors, Formulas

 
188. How to Break Links Between a Chart and a Worksheet In Microsoft Excel, a chart can be unlinked from its corresponding worksheet by converting the references in the series formula to values. To convert all the references in the series formula to values, follow these steps: Make sure both documents... KB#214139. Keywords: Charts, SERIES, Errors, Formulas, Links

 
189. Excel site index Microsoft Excel 2002 Formulas (With CD) Index lists created using 'MeadInKent... Keywords: Charts, SERIES, Criteria, Rank, Dates, XML, Format, Styles, Arrays, Errors, Formulas, Macro, VBA, HTML, Hyperlinks, Web, Validation, FV, PMT

 
190. How to use Shared Workbooks with Different Versions of Excel, Including Excel 2000 This step-by-step article explains how to share a Microsoft Excel workbook with other users. If you want several users to work in the same Excel workbook simultaneously, you can save the workbook as a shared workbook. Users can then enter data,... KB#269860. Keywords: Format, Errors

 
191. Cannot Suspend Computer with Windows 2000 Offline Files Open When you edit a document that was made available offline using Windows 2000, you cannot suspend the computer and may receive the following error message: Windows cannot go on standby because Microsoft Office documents or application components... KB#257456. Keywords: Errors, Files

 
192. How to Preserve Formulas When Saving as a Text File In Microsoft Excel, it is sometimes necessary to save your document as a text file to fix corruption in the... KB#153490. Keywords: Formulas, Text Files

 
193. A macro to fill in blank rows Thanks to Mike who provided this week's question: I'm trying to write a macro and am new at this. I have a spreadsheet that is hundreds of pages long and many columns wide. I would like the macro to search only the "A" column for ""(no data) and then use the data from the previous row, selecting... Keywords: Formulas, VBA, Macro, Web

 
194. Error Message: The Document Caused a Serious Error the Last Time It Was Opened When you try to open a file that Microsoft Excel or Microsoft Word could not open previously, you receive the following error message: The document ' Filename ' caused a serious error the last time it was opened. Would you like to continue opening... KB#286017. Keywords: Errors, Corrupted, Files

 
195. Delete Links Wizard Available The Delete Links Wizard is an add-in file for Microsoft Excel that you can use to delete links to external files in one or more workbooks. Currently, the only way to perform this task is to manually delete the links or to use a Microsoft Visual... KB#188449. Keywords: Add-In, Query, Download, Formulas, Links, Macro, VBA, Files, Text Files

 
196. Excel -- Data Entry -- Fill Blanks Fill Blank Cells Fill Blank Cells Some worksheets contain cells that have been left blank, in order to make the headings and subheadings easier to read. However, if you want to sort or filter the list, you need to fill in the blanks, by copying the value from the first filled cell above the blank.... Keywords: Sorting, Errors, Formulas, VBA, Macro

 
197. Parameter Query Does Not Refresh Automatically When you type a parameter for a Web or database query into a cell, Microsoft Excel does not refresh the query, even though you selected the Refresh automatically when cell value changes check box in the Parameters dialog... KB#236990. Keywords: Query, Formulas, Links, Web

 
198. Determine Parse Position Keywords: Arrays, Formulas, Links, Printing, VBA, Parse

 
199. How to Sort Cells Without Sorting Linked Cells If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated with the sorted data. This article contains an example that sorts linked data without affecting cells that are linked to... KB#174537. Keywords: Sorting, Formulas, Links, OFFSET

 
200. Displaying AutoFilter criteria Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that... Keywords: Charts, AutoFilter, Criteria, Format, Errors, Formulas, Printing, VBA

 

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