Book Home Page Bloglines 1906 CelebrateStadium 2006 OfficeZealot Scobleizer TechRepublic AskWoody SpyJournal Computers Software Microsoft Windows Excel FrontPage PowerPoint Outlook Word Host your Web site with PureHost! 
Saturday, February 20, 2010 – Permalink – Selection AddressWhat's the count". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell." Display number of Rows and Columns See all Topics excel Labels: Customize, Formulas, Macros, Reference, Shortcuts, Tips, VBA <Doug Klippert@ 3:59 AM
Comments:
Post a Comment
Wednesday, January 13, 2010 – Permalink – Text Files to ExcelA hard way to do an easy jobText files can be imported or just pasted into Excel. If you want to learn more about SQL statements and noncode coding, look at this blog:"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string." MSDN Excel Blog See all Topics excel <Doug Klippert@ 3:53 AM
Comments:
Post a Comment
Wednesday, December 30, 2009 – Permalink – Chart Null Data GapsFill in the spacesWhen creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero. By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line. One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells. Another way is to go to Tools>Options and choose the Chart tab. Pick how you want data plotted  with gaps, as zero, or interpolated. Microsoft also has this suggestion: Gaps between the dates "If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a timescale axis. Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box. See all Topics excel <Doug Klippert@ 3:18 AM
Comments:
Post a Comment
Sunday, December 06, 2009 – Permalink – Curvesand More
"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar  and frequently must convert from one to the other. See all Topics excel <Doug Klippert@ 3:02 AM
Comments:
Post a Comment
Tuesday, May 26, 2009 – Permalink – Name that RangeHow to use names in ExcelNamed ranges are one of the more powerful tools in Excel. Jan Karel Pieterse of JKP Application Development Services has written a tutorial that will help you understand this technique. Here are some of the chapter titles:
See all Topics excel <Doug Klippert@ 3:54 AM
Comments:
Post a Comment
Tuesday, May 19, 2009 – Permalink – UDF is not a Baby AlienThings should to functionFrank Rice has written a "show how" about creating functions that are not included in the box. "Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other builtin Excel functions. frice's Weblog Here are some other links: Vertex42.com: User Defined Functions Support.Microsoft.com: Functions to Calculate Light Years See all Topics excel <Doug Klippert@ 3:41 AM
Comments:
Post a Comment
Thursday, April 23, 2009 – Permalink – Lookup, Down, and SidewaysA very useful Excel featureExcel does not have "relational" tables like database applications such as Access. You, however, can make use of database functions including the ability to look up values in a table based on a value. You could, for instance look up a salesperson's records based on an employee ID. All 'Bout Computers has an article describing "Lookups". Using VLOOKUP, HLOOKUP, INDEX, and MATCH in Excel to interrogate data tables John Walkenbach has a book "Excel 2003 Formulas" with a 24page chapter on Lookup functions and other database/list tricks. Chip Pearson talks about lookups on his site as well. Aaron Blood's site offers download files explaining lookup. See numbers 36, 37, and 44. One of the zipped Workbooks (number 35) Lookup.zip, is a study in lookup methodology. Daily Dose of Excel: VLookup on Two Comumns See all Topics excel Labels: Formulas <Doug Klippert@ 3:01 AM
Comments:
Post a Comment
Sunday, November 23, 2008 – Permalink – Array FormulasGood orderly directionAn array is defined as "An orderly arrangement". It can be thought of as a collection of data packaged in a container. The individual items in the container can be selected by referring to their location; first, second, and so on.
"Have you ever sat in front of your monitor pulling your hair out trying to identify duplicate entries in a list? If so, you should learn about Microsoft Excel's array formulas. In fact, you can use array formulas to perform calculations that are otherwise impossible in Excel, and you can enhance the power of some of the program's existing functions." Excel's Array Formulas By Helen Bradley Chip Pearson: Introduction To Array Formulas "Array Formulas are formulas that work with arrays, instead of individual numbers, as arguments to the functions that make up the formula"
<Doug Klippert@ 3:33 AM
Comments:
Post a Comment
Wednesday, October 01, 2008 – Permalink – Data ComparisonNo formulasThe Data Consolidation technique allows you to compare lists quickly and easily. With the Consolidation technique, you can identify the number of duplicate entries in two or more lists without using a formula. (not that it's easier, just that there are no formulas)
The numbers appears in Column B are the totals of the list number in Column B. If the result = 1, the name appears in List 1 and does not appear in List 2. If the result = 2, the name appears in List 2 and does not appear in List 1. If the result = 3, the name appears in both lists (1+2=3). The action is not dynamic, so if you make changes, the Consolidation must be rerun. From: "Mr Excel ON EXCEL" (Holy Macro Books) Also see: John Walkenbach: Comparing Two Lists With Conditional Formatting Chip Pearson: Duplicate And Unique Items In Lists Here's a more complex method: Microsoft Office Online: Use Excel to compare two lists of data Also: BetterSolutions.com: What are Consolidated Worksheets ? See all Topics excel <Doug Klippert@ 4:16 AM
Comments:
Post a Comment
Thursday, September 04, 2008 – Permalink – Running Total in CommentCircular solutionYou can't have a worksheet formula that looks like this: =C3+C3 But you can do something similar if you use VBA and store the results in another location. "In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a noncalculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this by storing a running total in a cell comment."
<Doug Klippert@ 12:06 PM
Comments:
Post a Comment
Friday, August 01, 2008 – Permalink – Rank FormattingHighlight the bestUse Conditional formatting to highlight the rank of items in a list. Select the range. Go to Format>Conditional Formatting.... Change the first box to "Formula Is". Enter the following formulas. (Click Add to set the 2nd and 3rd Condition.) =RANK($A2,$A$2:$A$13)=3 =RANK($A2,$A$2:$A$13)=2 =RANK($A2,$A$2:$A$13)=1 (Notice the three way tie for third.) Does a tie for first or third make sense? If you want a unique rank, try a formula like: =RANK(A2,$A$2:$A$13)+COUNTIF($A$2:A2,A2)1 This will rank the numbers in the order they appear in the list. For a detailed discussion of ranking see: Chip Pearson: Ranking Data In Lists (There is a workbook you can download) See all Topics excel <Doug Klippert@ 2:42 AM
Comments:
Post a Comment
Monday, July 14, 2008 – Permalink – Column(s) FunctionVLOOKUP"Excel will adjust cell references in formulas when you insert or delete rows or columns.
Also: <Doug Klippert@ 5:25 AM
Comments:
Post a Comment
Thursday, July 03, 2008 – Permalink – Show Formulas in Cell CommentsDisplay propertiesSelect the cells and then run this macro:
<Doug Klippert@ 5:11 AM
Comments:
Post a Comment
Tuesday, June 24, 2008 – Permalink – AutoFilterOnly what you wantExcel has a tool to sort lists with a number of criteria. Select a single cell in the table and go to Data>Filter AutoFilter. Click on the down arrow next to the field name and choose Custom. The illustration shows how to set up a filter that displays data between two dates.
"Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."
"If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool. Custom Views can be easily set up based on your Autofilter criteria. Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them." "The Display Filter Criteria doesn't work well. It only works when after set the filter, the cell with the formula is selected. Then press F2 en after that push the Enter key. Then it works. but the cell with the formula isn't updated automatically..." # posted by W. van Dam : 7/06/2005 See all Topics excel <Doug Klippert@ 4:22 AM
Comments:
Post a Comment
Friday, April 18, 2008 – Permalink – Excuse Me Your Formula's ShowingFormatting slipTry this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range. Select the C column and format it as Text. (Right click choose Format Cells  on the Number tab choose Text) In cell C1 enter a function, such as =sum(a1:b1) With C1 still selected, double click the Fill handle (the tiny box at the lower right corner of the cell.)
Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display. Labels: Formulas, General, Troubleshooting <Doug Klippert@ 7:31 AM
Comments:
Post a Comment
Tuesday, April 08, 2008 – Permalink – Date and Time EntryMonth Day, Day MonthQDE An Excel Date Entry AddIn Ron de Bruin "QDE is a fullyfunctional Excel Addin that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."
<Doug Klippert@ 6:18 AM
Comments:
Post a Comment
Tuesday, February 19, 2008 – Permalink – Zero 0Zero is nothingIf a zero isn't worth anything, why show it? Here is a Microsoft tutorial about how to deal with zilch:
Hide Zeros See all Topics excel <Doug Klippert@ 9:22 AM
Comments:
Post a Comment
Sunday, December 23, 2007 – Permalink – Date ArithmeticThe drunken cousinWorking with dates has a few twists. Excel believes that time began on January 1, 1900. Each day since then is counted so that September 1, 2003 in Excelspeak would be → 37,865. 9/1/03 7:33 A.M. is a decimal → 37865.31458333333 When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2), Excel displays the odd answer of → 2/27/1902. Excel formats the result of a formula with the same format as the source cells, Rightclick the formula cell (=A1A2). Select Format Cells ..., and then choose a Number format with zero decimals. The correct number of days → 789 will now be displayed. Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family." =DATEDIF(EarliestDate,LatestDate,Interval) =DATEDIF(A2,A1,"d")
Also: "Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions addin (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999." MS Knowledge Base: How To Use Dates and Times in Excel See all Topics excel <Doug Klippert@ 7:14 AM
Comments:
Post a Comment
Monday, November 12, 2007 – Permalink – All the BasicsAll(most) all you need to knowOffice.Microsoft.com has a short demo that shows you the main things anyone needs to know about Excel. There are many thousands of users who find that this is all they ever need.
Use simple formulas to do the math See all Topics excel <Doug Klippert@ 8:00 AM
Comments:
Post a Comment
Thursday, August 30, 2007 – Permalink – Calculate Running TotalUsing the OFFSET functionAdding up a running balance can be frustrating when new data is added or old transactions are removed. "How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."
<Doug Klippert@ 8:15 AM
Comments:
Post a Comment
Sunday, June 17, 2007 – Permalink – Tips and FormulaeFunctions and MacrosI'm always looking for Excel sites. A fresh perspective can make the view more clear. While he does approach from a Mac angle, the Excel world welcomes those of all persuasions. J.E. McGimpsey's XL Pages Here are some of the tips:
See all Topics excel <Doug Klippert@ 8:07 AM
Comments:
Post a Comment
Wednesday, May 16, 2007 – Permalink – Calendars  PerpetualIt's that year again
DotXLS.com: Perpetual calendars
<Doug Klippert@ 6:13 AM
Comments:
Post a Comment
Tuesday, January 23, 2007 – Permalink – Result is a PictureIf 4, show kumquatAllen Wyatt has a cool procedure that will let you show a picture of an object on your spreadsheet depending on a value. Maybe a snow suit when it's 29 or, say, a pair of bloomers when the computed temperature is 70. The procedure does not use any VBA, just equations and bright thinking. ExcelTips.VitalNews.com: Display Images based on a Result See all Topics Labels: Formulas <Doug Klippert@ 5:32 AM
Comments:
Post a Comment
Monday, January 15, 2007 – Permalink – Worksheet NameFormula constructionThere may come a time when you need to display the name of a worksheet. This formula will do the job: =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31) =CELL("filename",$A$1)returns the path, the Workbook name and the Worksheet name. (C:\Documents\[April.xls]\Costs) =MID(text,start_num,num_chars)selects the text that starts at a certain point and goes on for a certain number of characters. The formula, as written, looks at the full path and selects the first time a closing bracket (]) is found. It then moves 1 character to the right and displays the results up to 31 characters. (A worksheet name cannot be more that 31 characters long. You could include a reference to that cell on other worksheets. See all Topics Labels: Formulas <Doug Klippert@ 8:15 AM
Comments:
Post a Comment
