Moved to the Excel Twitters archive:
Excel Twitters 20090417
http://exceltheatre.com/blog/archives/2009/04/17/excel-twitters-20090417/
 
|
Occasionally you might get an email in which the sender mentions someone else. In the example shown below, the client promises to ask Xavier to contact you. That name sounds vaguely familiar, but perhaps you can't remember exactly who Xavier is, or what role he has in the client's company. If you're using Outlook 2007, it can help you remember. Who Is...Right-click on a name in the body of an email, to open a shortcut menu. Click Who Is..., and Outlook will check your contact information, to see if there's anyone with that name. If there's more than one person with that name, a Check Names dialog box will open, with a list of possible matches.
Click on a name in the list, then click OK, to open that person's Contact information. If there's only one person with the name, their Contact information will open Continue reading Identify a Contact in Outlook In Excel 2007, conditional formatting options include Icon Sets, such as coloured flags, stop lights, and other symbols.
You can use these symbols to show what values are going up, or which departments are exceeding their budgets. Excel 2003Icon Sets aren't available in earlier versions of Excel, but you can use conditional formatting, a formula, and the Wingding font to show symbols in an adjacent column. In this example, coloured shapes appear in cells C3:C7, linked to the values in column B. For values less than 10, a red circle will appear For values greater than 30, a green square will appear. For all other values, a yellow diamond will appear.Create the Formulas You'll create an IF formula, and use the WingDing font to show the result as a symbol. In WingDing font, l (lower case L) is a circle, n is a Continue reading Conditional Formatting Icons in Excel 2003 Have you ever opened a workbook and found that the column headers show numbers instead of letters? The formula look strange too, showing references like RC[-1] instead of D2. This is R1C1 reference style -- a handy feature, and I use it sometimes when programming or setting up a workbook. Numbers on the column headings make it easier to set up formulas that need a column number, such as VLOOKUP. I don’t have to get fingerprints on my screen, as I count across to column R, where the lookup value is. Why It Happens Maybe you’ve never heard of R1C1 reference style, and certainly didn’t change any settings. If you didn’t turn that option on, why did the numbers suddenly appear? Probably because someone sent you a workbook, and that’s the first Excel file that you opened today. The first workbook that you open, when opening Excel, sets the Continue reading Excel Column Headings Show Numbers It seems simple enough, but sorting data in Excel can go horribly wrong, if you aren't careful. For example, you could sort a list of names, but miss the phone numbers in a nearby column. When you're finished, all the names are associated with the wrong phone number. Here are some tips for trouble free sorting: 1. Make a backup copy of your file before you sort the list. If there are problems with the sorted list, you can use a copy of your backup file instead. 2. Save the file before you sort the list. Then, if the list get scrambled, you can close the list without saving it again. 3. Before you sort, select all the cells in the list. This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list -- but Continue reading Safely Sorting Data in Excel With a bit of programming, you can make it easy for users to enter data in an Excel workbook, and keep them away from the stored data. Dave Peterson created a sample workbook with a worksheet data entry form.
There are data validation drop down lists in cells D5 and D7, and quantity is typed in cell D9. Click the Add to Database button, and the new record is added at the end of the database, which is a list on a different sheet. View the Stored RecordsIn Dave's workbook, you could click the View Database button to go to the database sheet, and review or edit the order records. In some cases you might prefer to hide the database sheet, to protect the records, but still allow users to view the existing data. I've added a few buttons to Dave's workbook, to allow users Continue reading Worksheet Data Entry Form in Excel You can use Data Validation to limit the total amount that users enter in a group of cells. For example, if budget is $3500, you can prevent entries that will go over that amount. Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal. The user can enter budget amounts in cells C3:C7. Cell C8 sums the amounts entered in cells C3:C7. Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8). Add the Data Validation The data validation is applied to cells C3:C7, because this is where the data entry occurs. Select cells C3:C7 On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)Choose Allow: Custom For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to the Continue reading Limit the Total Amount Entered in Excel Last week, the UK Excel User Group Conference was held at the Microsoft offices in London. You can see a few conference photos by Bob Phillips, including shots of speakers Nick Hodge, Simon Murphy and Andy Pope, and a few pictures of the attendees. It was a free event, hosted by Microsoft, and filled to capacity quickly after registration opened. There may be another conference in the fall, and I'll post the details here if one is announced. There's also a list of upcoming Excel Events on my website. ________________________ Today is the publication date for Pro Excel Financial Modeling: Building Models for Technology Startups, by Tom Y. Sawyer. Drawing from his extensive experience with technology startup companies, Tom explains the business thinking behind financial modeling. Then, using a step–by–step approach, he shows how to develop financial models in Excel. The book includes extensive case studies and you can download the Excel templates from the Apress website. I was the technical editor for the book, and was impressed by Tom's knowledge, and his ability to clearly explain the complex topics that the book covers. The templates and screen shots are from Excel 2007, but you could adapt the techniques for any version of Excel. What you'll learn: Business thinking behind successful financial modeling aimed at investors. How to communicate effectively with investors. Advanced modeling with Excel, including Cost of Information Technology, Customer ROI, Cost of Sales and Marketing,Continue reading Pro Excel Financial Modeling: Building Models for Technology Startups Moved to the Excel Twitters archive: Excel Twitters 20090403  |
|
|
Copyright © 2012 Contextures Blog - All Rights Reserved |
||
Recent Comments