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! 
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
Saturday, December 12, 2009 – Permalink – Display Row, Column HeadingsUser FunctionHere's an odd little use of functions. If you want to display the Row number on a spreadsheet, the formula =Row()works just fine. You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update. Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B". The following formula extracts the Column letter: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") To break it down: =ADDRESS(row_num,column_num,abs_num) This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address. The formula will produce a result such as "AA1". SUBSTITUTE(text,old_text,new_text) This function looks at the address, i.e. "AA1". It replaces the Row number character ("1") with a null or empty value (""). The formula will produce a result such as "AA". Also see Daily Dose of Excel by Dick Kusleika. Dick mused:'
Of course Office 200710 has taken it up to 16,284 columns. See all Topics excel <Doug Klippert@ 3:11 AM
Comments:
Post a Comment
Wednesday, September 30, 2009 – Permalink – When 28 is 30How long is a month?When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer. If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned. You expect a value of 30 days to be returned for every full month. (12*30=360) This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function. To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax: =DAYS360(cell number of start date,cell number of end date,TRUE) Using FALSE or omitting the third parameter uses the U.S. method Support.microsoft.com An unexpected value with the DAYS360 function See all Topics excel Labels: Functions, Reference, Troubleshooting <Doug Klippert@ 3:19 AM
Comments:
Post a Comment
Thursday, August 20, 2009 – Permalink – Still More FunctionsNever EnoughLaurent Longre Has put together an Excel addin with 65 more functions. Here are a few you might find useful: MOREFUNC.XLL, 65 addin worksheet functions See all Topics excel <Doug Klippert@ 3:14 AM
Comments:
Post a Comment
Monday, June 01, 2009 – Permalink – Count the ColorsI bid 3 RedWhat if you would like to know the color name or to count or to sum cells by a fill color? There is no builtin function in Excel.Sum and Count by fill color Chip Pearson: Working with Cell Colors See all Topics excel <Doug Klippert@ 3:17 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
Wednesday, February 25, 2009 – Permalink – Hide DigitsSimple obfuscationThe kid said, "Daddy, I know the secret password! It's star, star, star, star!" **** You can use functions to hide parts of sensitive data. Social Security Number 555555555 =CONCATENATE("*****", RIGHT(B2,4)) Combines the last four digits of the SSN with the "*****" text string (*****5555) Credit Card Number 5555555555555555 =CONCATENATE(REPT("****",3), RIGHT(B3,4)) Repeats the "****" text string three times and combines the result with the last four digits of the credit card number (************5555) Microsoft Office Online: Display only the last four digits of identification numbers See all Topics excel <Doug Klippert@ 3:14 AM
Comments:
Post a Comment
Tuesday, November 25, 2008 – Permalink – Information FunctionsWho, What, WhereIf you need to know if automatic calculation is on or off or the number of worksheets that are active, Excel can display the INFO. System Information:
ExcelVBA.com: <Doug Klippert@ 3:12 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
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
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
Saturday, March 29, 2008 – Permalink – Week NumbersWho's counting?For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.
If your week starts on a different day, you can use the Analysis ToolPac function:
"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."
datepart("ww",[DateField],7,1)
<Doug Klippert@ 7:24 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
Thursday, November 01, 2007 – Permalink – Loan PaymentBasic tutorialMicrosoft provides a number of learning activities related to fundamental tasks. Here's one that walks the student through a worksheet designed to calculate interest and total payment for a purchase, based on different loan terms. "This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing? Also: <Doug Klippert@ 5:55 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, April 22, 2007 – Permalink – Numbers to WordsCardinal numbers
<Doug Klippert@ 6:36 AM
Comments:
Post a Comment
Monday, March 12, 2007 – Permalink – Workbook WikiInfo sourceWikipedia is an interesting tool because of the democratic nature of its definitions and information. Mr. Excel has constructed an Excel Wiki: "What is the Excel Wiki? ExcelWiki.Functions See all Topics excel <Doug Klippert@ 6:56 AM
Comments:
Post a Comment
Wednesday, January 31, 2007 – Permalink – Statistical FunctionsDefinitions and listExcel contains a slew of functions relating to statistical analysis. That's a slew not a skew.
Statistical Functions Troubleshooting  Statistical See all Topics Labels: Functions <Doug Klippert@ 6:49 AM
Comments:
do you know of a way to take time in hh:mm:ss and convert that number into a decimal equivilant. I was looking for a excel spreadsheet or template, but cannot find one any where. I do this alot at work, and it sucks!
Post a Comment
