Home > Excel Help Portal > Using Excel Formulas

<<  (Page 4)  Previous              Next  (Page 6)  >>

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.

201. How to Remove Links from a Worksheet In Microsoft Excel, you can change and move links in the Links dialog box. However, you cannot use the Links dialog box to remove links. This article tells you how to remove links from Excel... KB#214127. Keywords: Formulas, Links, Defined Name

 
202. How to use SUM Function on Only Odd or Even Rows in Excel 2000 This step-by-step article explains how to use the SUM function on only the odd rows or the even rows in a range. When you use any of the worksheet functions in Excel, and you have a range argument that includes either hidden rows or columns, the... KB#213961. Keywords: Arrays, Formulas

 
203. Sharing AutoCorrect shortcuts Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them? AutoCorrect, which debuted in Excel 95, can correct common spelling... Keywords: Charts, AutoCorrect, Format, Errors, Formulas, Printing, VBA, Macro, File Name

 
204. Errors Quitting Office Apps with IntranetWare Network When you quit a Microsoft Office 2000 program that runs on a Novell IntranetWare network, you may receive the following message This connection must be maintained for Novell Directory Services use on tree tree name . It can only be removed... KB#206734. Keywords: Query, DLL, Third-Party, Errors

 
205. How to Suppress Error Values on a Worksheet In Microsoft Excel, you can prevent error values (such as #N/A, #VALUE!, #REF!, and #NUM!) from being... KB#87299. Keywords: Colors, Custom Number Formats, Fonts, Errors, Formulas, Printing

 
206. Identify formulas using Conditional Formatting How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag. This clever technique was submitted by David Hager. It... Keywords: Charts, Format, Formulas, Printing, Macro, INDIRECT

 
207. How to Create a Formula to Correctly Evaluate Blank Cells In Microsoft Excel, when you use a formula that tests for a zero value, you may see unexpected results if the cell is blank. Microsoft Excel interprets a blank cell as zero, and not as empty or blank. Therefore, any cells that are blank are... KB#214244. Keywords: Formulas

 
208. Parsing phone numbers Carla writes: how do you copy & paste a subtotaled table onto a worksheet?Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all... Keywords: Download, Formulas, VBA, Web

 
209. Excel Function Help Carla writes: how do you copy & paste a subtotaled table onto a worksheet?Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all... Keywords: Download, Formulas, VBA, Web

 
210. Moving Subtotaled Data Carla writes: how do you copy & paste a subtotaled table onto a worksheet?Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all... Keywords: Download, Formulas, VBA, Web

 
211. Help using Excel spreadsheet functions - Offset, Choose These functions are both very useful for creating worksheets which allow users to create alternative reports - for example by choosing a different month number. Offset returns a value from a cell which is at a user defined point in your spreadsheet and is in the format: =OFFSET(BaseRef, Rows, Cols,... Keywords: Charts, Errors, Formulas, OFFSET, CHOOSE

 
212. Color Rows by Criteria This piece of re-usable code is called by any of the routines and already “knows” what the ColorCode index number... Keywords: Charts, Criteria, Borders, Colors, Formulas, Links, VBA

 
213. How to Sum Either Odd or Even Rows Only When you use any of the worksheet functions in Microsoft Excel, and you have a range argument that includes either hidden rows or columns, the cells in these hidden areas are still used by the function. For example, if you have the following in... KB#136738. Keywords: Arrays, Formulas

 
214. How to Use the OFFSET Function This article describes how to use the OFFSET function to return the value of a cell that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent... KB#324991. Keywords: Formulas, OFFSET

 
215. Frequently Asked Questions About Using Links in Excel 2002 A link in Microsoft Excel can be any of the following: A reference to another workbook (sometimes called an external reference ) A link to another program (sometimes called a remote reference ) A link to data on the Internet, the World Wide Web,... KB#293418. Keywords: Errors, Links, Defined Name, Error Message, HTML, Hyperlinks

 
216. Comparing Two Lists With Conditional Formatting Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data. The first list is in A2:B19, and this range... Keywords: Charts, Colors, Format, Formulas, Printing, COUNTIF

 
217. Errors For some background information, please see HELP (F1) for ISERROR. ISERR Value refers to any error value except #N/A. ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). ###### The ##### indicates that the number value for the cell as formatted is too... Keywords: Dates, Format, Errors, Formulas, Macro, Toolbar, Comments, INDEX

 
218. How to find the Secant, Cosecant, or Cotangent of an Angle in Excel 2000 Microsoft Excel 2000 has built-in functions for a large number of trigonometric functions but does not include all such functions. However, you can derive functions from existing functions by using an Excel formula. Some functions that you can... KB#213887. Keywords: Formulas, PI

 
219. Locate phantom links in a workbook Q. Whenever I open a particular Excel workbook, I get a message asking if I want to update the links. I've examined every formula in the workbook, and I am absolutely certain that the workbook contains no links to any other file. What can I do to convince Excel that the workbook has no links?... Keywords: Charts, SERIES, Format, Formulas, Links, Printing, Object

 
220. How to Display the File Name of a Worksheet Without Using a Macro This article describes how to create a formula that displays the file name of a worksheet in a cell. For example, for a file with the path C:\Excel\Data\Test.xls the formula returns... KB#214084. Keywords: Arrays, Errors, Formulas, File Name, RIGHT, MAX, ROW, MID

 
221. Copying an Embedded Chart to a Chart Sheet 3. To paste the copied chart to the blank chart sheet, select the blank chart sheet tab (this requires two clicks, one to turn off the in-place editing of the chart and another to select the chart sheet), and then click Paste on the Edit menu. You will now have a copy of the embedded chart on its... Keywords: Charts, Formulas, Links

 
222. Find the longest entry in a range of text entries Yes, Ketan this formula would be very helpful. There are a whole class of super-formulas in Excel that can solve problems like this one in a single cell. Veteran MrExcel readers know this is my very favorite tip. It will move you to the front of the pack, ahead of 95% of other Excel users. You can... Keywords: Formulas, Class, VBA, Web, MAX

 
223. Write a Text File My friend came to me with a text file that he needed to extract 3 elements on suceeding lines and re-arrange them into a new order. Bringing the 4,800 row text file directly into Excel caused things to be split in an uncomfortable order and was going to take a lot of programming. Hope this... Keywords: Format, Arrays, Formulas, Links, Printing, VBA, Text Files, Parse

 
224. How to Reverse the Sign on a Difference Assuming that the “Net Proceeds” are in Column A, and “Total Invested” is in Column B, try the following formula:=A1 – B1In your example, if A1 = 12,000 and B1 = 10,000 then the formula would show a POSITIVE 2,000. If Net Proceeds is smaller than Total Invested then it would be a NEGATIVE number.... Keywords: Formulas, VBA, Web

 
225. An Excel formula to calculate weighted averages To calculate a general indicator of a selection of values you could calculate the arithmetic mean - generally known as the average. If measuring the average price of foodstuffs you could take a list of products available and then calculate the average. E.g. Bread (60) + Milk (30) + Beef (300) +... Keywords: Sales, Arrays, Formulas

 
226. Invalid Page Fault Changing PivotTable Data Source When you use the PivotTable Wizard to change the location of the source data for your PivotTable, Microsoft Excel stops responding and you receive an error message similar to the following: This program has performed an illegal operation and will... KB#248225. Keywords: Errors, PivotTables, Error Message

 
227. Excel -- Pivot Tables -- Dynamic Data Source Dynamic Data Source 1. Pivot Tables -- Introduction Getting Started Use a Dynamic Data Source 2. Pivot Tables -- Data Field Layout 3. Pivot Tables -- Show and Hide Items 4. Pivot Tables -- Clear Old Items Many books and web sites have information on creating Pivot Tables. For a brief introduction,... Keywords: Download, Formulas, PivotTables, PivotChart, Web, OFFSET, COUNTA

 
228. You Cannot Update Links to Files on Secure Web Server You may not be able to update links in a Microsoft Excel workbook that is located on a secure Web server that uses Web Folders. For example, you may experience the following behaviors: When you click Links on the Edit menu to update the... KB#811542. Keywords: Errors, Formulas, Links, Error Message, Files, Web

 
229. Concatenate cells Valerie writes: I am an Excel novice. I have imported a file into Excel. The zip code is broken into 2 cells. The 5 digit part of the zip code is in column E, the 4 digit part of the zip code is in column F. I need them concatenated in order to do a mail merge. =E2&"-"&F2 After copying the formula... Keywords: Formulas, VBA, Web

 
230. Summarize data by hour Kevin writes: I monitor my glucose levels several times a day. After a month of readings, I want to see a table showing the average glucose level in each hour of the day. The sample file that Kevin sent had a date and time in column A and the glucose level in column B. I suggested the following:... Keywords: Formulas, PivotTables, VBA, Web, HOUR

 
231. Links to Closed Workbook with Defined Names Update Slowly When you choose to update external links to a closed Microsoft Excel workbook, it takes longer than it did in earlier versions of... KB#248173. Keywords: Links, Files

 
232. Programatically copy macro to new workbook customization of plan omited for brevity Sheets(Array("Menu", "Plan")).Copy NBName... Keywords: Export, Arrays, Errors, VBE, VBA, Web

 
233. Use Set to create an Object Variable instead of a regular variable when referencing cells Object variables are pretty cool. In the old tips at MrExcel, I would use regular variable like these:OrigName = ActiveSheet.NameWorksheets(OrigName).CopyThere is a better way. You can define a variable to be any object, such as a worksheet. There are several advantages to this. It is shorter to... Keywords: Errors, Macro, Object, VBA, Web

 
234. Parse Text File The data as it comes out of the accounting system looks very un-professional but improves when I place it in my template. CompArray = Array(5, 33, 10, 10, 10, 10, 10, 9) SplitArray = Array(6, 12, 45, 57, 68, 81, 112, 122) PasteArray = Array(1, 2, 3, 4, 5, 6, 9, 10) DetPlace = 0 Do While DetPlace =... Keywords: Arrays, Errors, Formulas, Links, VBA, Templates, Text Files, Parse

 
235. Excel - Protecting cells You may choose to protect the contents of a cell in order to prevent accidental deletion or to stop someone else from wrecking the integrity of your calculations. (see spreadsheet testing). If you have a complex formula which has been properly tested it may be advisable to prevent further changes... Keywords: Format, Errors, Formulas, Links, Password, Validation

 
236. How to Use Visual Basic for Applications in Excel You can create macros with Microsoft Visual Basic for Applications (VBA) to automate common actions in Microsoft Excel. This article points to resources that can help you learn more about using VBA for this... KB#304494. Keywords: Arrays, Errors, Macro, VBA

 
237. Index of Help for Excel functions, graphs and testing A function is a special key word which can be entered into a cell in order to perform a process to some data which is appended within brackets. = FunctionName(Data) The data (or argument in proper terminology) often includes a range of cells. Excel automatically recognises the names of these... Keywords: Years, Formulas, Links, Toolbar, AVERAGE, COUNT, COUNTA

 
238. How to turn Off Function Argument ToolTips in Excel 2002 In Microsoft Excel, you can complete functions with the aid of the ToolTip feature, which displays ToolTips by default when you begin to type a formula in the formula bar. These ToolTips also make it easier to obtain additional help for a... KB#289146. Keywords: Formulas

 
239. Transforming Data With Formulas This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data. The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case.... Keywords: Charts, Format, Formulas, Printing, PROPER

 
240. Copying Worksheet into Workbook Deletes Defined Name When you copy a worksheet into a workbook, existing defined names may be deleted. Formulas that refer to the deleted defined names return the #NAME?... KB#250825. Keywords: Charts, Errors, Formulas, Defined Name

 
241. Changing 'All Caps' to proper text The =PROPER() function will do this for most records.Let's say your data is in A2:G600Over in blank columns off to the right, enter a formula of =PROPER(A2)Copy this formula from, say H2 to H2:N600. Make the shape of this range of formulas match the shape of your original range. If you original... Keywords: Formulas, VBA, Web, PROPER

 
242. Excel Quits When Printing to a Printer with a Long Name When you attempt to print your workbook, Microsoft Excel quits unexpectedly. You receive the following error message, and your workbook is not printed: EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the... KB#248212. Keywords: Errors, Printing

 
243. Articles about troubleshooting errors in worksheets in Excel It can be challenging to find the cause of error messages in Microsoft Excel worksheets. This article lists many Microsoft Knowledge Base articles about troubleshooting error messages in Excel... KB#293412. Keywords: Arrays, Errors, Formulas, Window

 
244. Crash When Closing Excel After Closing Workbook with Circular References When you attempt to close Microsoft Excel after closing a workbook with circular references, you may receive one of the following error messages: Windows 95/98 EXCEL caused an invalid page fault in module EXCEL.EXE at 0167:303c4b08 Windows NT... KB#249076. Keywords: Errors, Circular References

 
245. How to Combine Cells of Information in Excel In Microsoft Excel, it is possible to combine, or concatenate, different cells of data into one cell. This article contains two... KB#280388. Keywords: Errors, Formulas, Macro, VBA

 
246. Excel UserTip: Making an Exact Copy of a Formula Range (Without VBA) Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1. By "exact," we mean a perfect replica -- the original cell references should not change. If the formulas contain only... Keywords: Charts, Format, Formulas, Sounds, Printing, VBA

 
247. Hiding your formulas Q. I've created some clever formulas, and I don't want anyone else to see them. Is it possible to hide the formulas but display the results? Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when... Keywords: Charts, Format, Formulas, Password, Printing

 
248. Error 1004 Opening Buy vs. Lease Analysis Report When you open your workbook, you receive the following error message: Error: 1004 Description: Method 'Range' of object '_Worksheet' failed Source: Localizationinit Module: modMain Procedure:... KB#251139. Keywords: Errors, Object, Web

 
249. An Excel VBA function to display cell contents In the example above, the user defined 'FormText()' function is used in cells P21 and P22 to display the contents of N21 and N22. The macro shown here should be entered into a Visual Basic module, either in specific workbook or in your (hidden) Personal.xls macro workbook. It can then be used like... Keywords: Formulas, VBA, Macro

 
250. Merging rows I will soon have about 400 spreadsheets with up to 6000 rows each in which I will have to apply the above process. And of course this has to be done in a three day turn-around period. Can you help??????Yes. You need to highlight all 6000 rows. Edit - GoTo. Click the Special button. Click Blanks and... Keywords: Formulas, VBA, Web

 

<< (Page 4)  Previous              Next  (Page 6) >>