|
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!
|
![]() Sunday, May 04, 2008 – Permalink – Conditional FormattingIf it's Tuesday, it must be mauve
This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes. Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur. You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used. However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100. GR Business Process Solutions: Also Labels: Excel <Doug Klippert@ 6:55 AM
Comments:
Post a Comment
Sunday, April 20, 2008 – Permalink – Power Utility Pak v6 - v7Excel the way you've wanted itJohn Walkenbach (J-Walk.com) has improved on the previous versions of PUP. It now includes more than 70 general purpose Excel utilities and 50 worksheet functions.
The blog has little or anything to do with Excel.
Labels: Excel <Doug Klippert@ 7:34 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: Excel <Doug Klippert@ 7:27 AM
Comments:
Post a Comment
Tuesday, April 08, 2008 – Permalink – Date and Time EntryMonth Day, Day MonthQDE An Excel Date Entry Add-In Ron de Bruin "QDE is a fully-functional Excel Add-in 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."
Labels: Excel <Doug Klippert@ 6:15 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:20 AM
Comments:
Post a Comment
Saturday, March 22, 2008 – Permalink – Default SaveChoose your own locationWhen you choose to save most Office files, the Save dialog box defaults to the Documents or My Documents folder. (The following directions work in 2007, but you need to click on the Office button in the upper left corner of the Window)
Change the folder where e-mail messages and attachments are saved Also: D.C. Everest school district Weston, WI: Office Default Paths If you don't want to change the default, but would like to be able to quickly go to an alternate site, open the Save or Save Attachment dialog box. On the left side of the box is the Places Navigation bar. If you click the Desktop icon, that location will be used to save the file. You can add spots to the bar. Browse to the specific folder. Highlight the folder and click the down arrow beside the Tools option. Select "Add to My Places." The file or e-mail attachment can then be saved where you want. [Edited entry from 11/05/2004] See all Topics Labels: Access, Excel, Outlook, PowerPoint, Word <Doug Klippert@ 7:42 AM
Comments:
Post a Comment
Thursday, March 20, 2008 – Permalink – Budget SpreadsheetFree money (tracker)One of the first things people do with Excel is make lists. Next they attempt to set up a budgeting worksheet. Michael Ham has a free downloadable budget spreadsheet. The formulas are protected, but you might consider it an exercise to figure out how they work.
Lulu.com See all Topics Labels: Excel <Doug Klippert@ 7:59 AM
Comments:
Post a Comment
Thursday, March 13, 2008 – Permalink – Accustom Yourself to ExcelShake hands with a worksheetAnneliese Wirth has written an article for Office.Microsoft.com about how to get used to the new user interface in Excel 2007.
Surviving the switch to Excel 2007 See all Topics Labels: Excel <Doug Klippert@ 7:14 AM
Comments:
Post a Comment
Sunday, March 09, 2008 – Permalink – Camera ToolSmile!(This is the per-2007 routine. For 2007 see the bottom of this tip. ) To create a linked picture of part of a spreadsheet for use elsewhere:
![]()
The process is a little different in 2007. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options. Labels: Excel <Doug Klippert@ 8:10 AM
Comments:
Post a Comment
Saturday, March 01, 2008 – Permalink – OLAP CubesMore dimensions than Star trek
<Doug Klippert@ 7:04 AM
Comments:
Post a Comment
Monday, February 25, 2008 – Permalink – Legacy files from 2007Go backRead this article closely. If you work in a situation where you need to work with legacy (pre-2007) files, it may be handy. If you do most of your work in 2007, I wouldn't bother. "When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats. Knowledgebase 935787 See all Topics Labels: Access, Excel, PowerPoint, Word <Doug Klippert@ 7:46 AM
Comments:
Post a Comment
Thursday, February 21, 2008 – Permalink – VBA, Named ArgumentsAn easier read
MsgBox(prompt[, buttons] [, title] [, helpfile, context]) When you work the MsgBox function this way, the order of the arguments can't be changed. Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as: MsgBox "Hello World!", , "My Message Box" Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value. For instance, the previous statement can be rewritten as: MsgBox Title:="My Message Box", _ (To find out a function's named arguments, select the function in your code and press [F1].) See all Topics Labels: Access, Excel, PowerPoint, Word <Doug Klippert@ 7:53 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 Labels: Excel <Doug Klippert@ 7:15 AM
Comments:
Post a Comment
Tuesday, February 12, 2008 – Permalink – Changes to 2007 ExcelDifferent can be goodMicrosoft has a Quick reference card that explains the highlights of the new features of Excel:
There are also links to:
Quick Reference Card See all Topics Labels: Excel <Doug Klippert@ 7:25 AM
Comments:
Post a Comment
Tuesday, January 29, 2008 – Permalink – Split the CostsSplit the sheets (?)Joe Chirilov presents a spreadsheet solution to a friendship breaker. Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time? You can download the spreadsheet here:Split_Costs.zip Labels: Excel <Doug Klippert@ 6:50 AM
Comments:
Post a Comment
Saturday, January 12, 2008 – Permalink – Spreadsheet DietFormatting bloatAvoid applying formatting to more than just the active area of your worksheet. Extraneous formatting will confuse Excel about the last cell in the spreadsheet. Depending on your OS and specific configuration, you could see symptoms ranging from Excel not responding to various error messages concerning page faults, low virtual memory, and access violations. You may see a warning message; "Too many different formats." To resolve this issue, make sure you select only the particular range of cells you want formatted when you apply specific formatting or select Format>AutoFormat. You can use Ctrl+End to see where Excel thinks the last cell is. For more information, check out: Labels: Excel <Doug Klippert@ 8:30 AM
Comments:
Post a Comment
Wednesday, January 02, 2008 – Permalink – More than SudokuGame with Excel"IT may be the biggest tool in the Office, but Microsoft Excel can be cool too" Here's an article on off-prescription Excel. Play with Excel Here are some of the games you can play for free:
ExcelGames.org See all Topics Labels: Excel <Doug Klippert@ 8:05 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 Excel-speak 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, Right-click the formula cell (=A1-A2). 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 add-in (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 [Edited entry from 10/24/2004] See all Topics Labels: Excel <Doug Klippert@ 7:10 AM
Comments:
Post a Comment
Monday, December 17, 2007 – Permalink – Reset Shortcut MenuContext menu redo
Sub ResetShortcutMenu() Macros that Customize and Control Shortcut Menus See all Topics Labels: Excel <Doug Klippert@ 5:13 AM
Comments:
Post a Comment
Sunday, December 16, 2007 – Permalink – Office VBA tricksVideo + Free codeQuick tips VBA Video "Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."
Ten Tips for Office VBA Developers [Edited entry from 10/22/2004] See all Topics Labels: Access, Excel, Outlook, PowerPoint, Word <Doug Klippert@ 5:00 AM
Comments:
Post a Comment
Monday, December 10, 2007 – Permalink – Chiropractics for ExcelHEADINGKnead and pound numbers Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel. Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another. I'm sure you've been faced with at least one of theses problems:
Manipulate and massage See all Topics Labels: Excel <Doug Klippert@ 4:48 AM
Comments:
Post a Comment
Sunday, December 02, 2007 – Permalink – Spreadsheet Boo-BoosDesign suggestionsA few links to sites with advice on spreadsheet design and error detection.
Labels: Excel <Doug Klippert@ 4:17 AM
Comments:
Post a Comment
Tuesday, November 27, 2007 – Permalink – Web DataNumbers from the etherExcel has had a feature called web query. Here's an add-in that makes it a little easier. "The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. ![]() Web Data Getting data from the Web in Excel See all Topics Labels: Excel <Doug Klippert@ 6:13 AM
Comments:
Post a Comment
Monday, November 19, 2007 – Permalink – Smart TagsDon't hideIn Excel, if you type a stock ticker symbol, the options offered on the Smart Tag are:
Smart tag functionality OfficeZealot.com: SmartTagz See all Topics Labels: Excel <Doug Klippert@ 7:33 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 Labels: Excel <Doug Klippert@ 7:58 AM
Comments:
Post a Comment
Sunday, November 04, 2007 – Permalink – Chart ArtApple π
Labels: Excel <Doug Klippert@ 5:36 AM
Comments:
Post a Comment
Saturday, November 03, 2007 – Permalink – Automation - VBA - Help FileOffice Wide
See all Topics Labels: Access, Excel, Outlook, PowerPoint, Word <Doug Klippert@ 6:45 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: Labels: Excel <Doug Klippert@ 5:53 AM
Comments:
Post a Comment
Friday, October 26, 2007 – Permalink – New Excel Web GrabberBeta Toy
Web Data Add-In From theExcel Blog team See all Topics Labels: Excel <Doug Klippert@ 6:19 AM
Comments:
Post a Comment
Thursday, October 25, 2007 – Permalink – 2003-2007 CompatibilityExchange the future and the past"Microsoft has added new file formats to Microsoft Office Word, Excel, and PowerPoint 2007. To help ensure that you can exchange documents between Microsoft Office releases, Microsoft has developed a Compatibility Pack for the Office Word, Office Excel, and Office PowerPoint 2007 File Formats"
Labels: Excel, PowerPoint, Word <Doug Klippert@ 6:40 AM
Comments:
Post a Comment
Thursday, October 18, 2007 – Permalink – Links to ExcelSpokes of the webThere's a lot of information out there. The problem is how to find it. Here is a site that contains links to Excel information arranged in topics:
Excel Links See all Topics Labels: Excel <Doug Klippert@ 8:02 AM
Comments:
Post a Comment
Wednesday, October 10, 2007 – Permalink – 65,534 Dollar QuestionThe eleventh place errorSure, you got a recall notice on your new car because the drink holder was the wrong size. Big deal, Excel 2007 also has/had a problem with some calculations. The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000. Excel 2007 hotfix package Calculation Issue Update See all Topics Labels: Excel <Doug Klippert@ 7:20 AM
Comments:
Post a Comment
Sunday, September 30, 2007 – Permalink – Data TablesUp one side; down another
Labels: Excel <Doug Klippert@ 7:41 AM
Comments:
Post a Comment
Wednesday, September 26, 2007 – Permalink – Send Your Template to MSGeek fameAccording to the Inside Office Blog, over 1 million people have downloaded free templates from Microsoft.
Upload your template See all Topics Labels: Access, Addendum, Excel, PowerPoint, Word <Doug Klippert@ 7:08 AM
Comments:
Post a Comment
Wednesday, September 19, 2007 – Permalink – Clippy's Revenge pre-2007Roll your own OA
If you want to play with it, see John Walkenbach's Create A Fake Clippy Programming the Office Assistant
With Assistant Here are a few of the actions:
[Edited entry from 9/14/2004] See all Topics Labels: Excel <Doug Klippert@ 7:42 AM
Comments:
Post a Comment
|