Excel Tips & Tricks Calculate the Days, Months or Years between two Dates in Excel
Yüklə 156,66 Kb.
|
|
Excel Tips & Tricks Calculate the Days, Months or Years between two Dates in Excel Use the DatedIf function to calculate the interval between dates in Excel. Here's how:
=DATEDIF ( start_date , end_date, unit )
Video demo : http://www.youtube.com/watch?v=F9dz30QkL7g Using IF Functions in Excel 2007 and 2010 
Download the If Function Worksheet (http://www.createthefuture.com/Excel_If.xls) and learn about the more complex uses of If functions. Quick Navigation in Excel To quickly move to the left, right, top, or bottom cell in a range of data, position the cursor at the beginning of the range and press: Ctrl + a cursor arrow To select a range of data, position the cursor at the beginning of the range and press: Shift + Ctrl + a cursor arrow
Using VLOOKUP in Excel VLOOKUP is a powerful Excel function which allows you to look up a value in an Excel list or table. For example, you could use this function to look up grades based on a percentage value:
To learn more about this function, check out the free online training from Microsoft VLOOKUP: What it is, and when to use it (http://office.microsoft.com/en-us/excel-help/vlookup-what-it-is-and-when-to-use-it-RZ101862716.aspx). Using Excel 2010 Sparklines (New Feature) A new feature in Excel 2010 called Sparklines can help you spot trends in your data. Sparklines are tiny charts that sit on top of a cell. Here’s how to create them:
Insert a Worksheet Based on a Template in Excel 2007
You can choose a custom template which you have created, one of the templates available when you click the Spreadsheet Solutions tab, or click the Templates on Office Online button and choose from hundreds of templates available from Microsoft. COUNTIFS in Excel 2007 We've had the COUNTIF function in Excel for a while. This function allows you to count records in a list that match a single criteria. What's completely new with Excel 2007 is COUNTIFS which allows you to count records based on multiple criteria. Sure, there were other ways of doing this before, but none as easy as the COUNTIFS function. Here's the syntax: =COUNTIFS(range1,criterion1,range2,criterion2,…,range_n,criterion_n) Makes it really simple to count records in a range that match the criteria you enter in the formula. If you need a little more help, check out this article on the Microsoft web site: Use the COUNTIFS function in Excel 2007 to analyze data (http://office.microsoft.com/en-us/excel/HA102369931033.aspx). SUMIFS in Excel 2007 This is just an extension of the CountIfs function above. Here's the syntax: =SUMIFS(SumRange,range1,criterion1,range2,criterion2,…,range_n,criterion_n)
AutoFill with Week Days Hopefully you are already familiar with the Excel AutoFill feature for filling ranges with the months or days of the week. But what if you need to fill an Excel range with just weekdays? Here’s how:
This tip works in Excel 2007/2010 as well as earlier versions. Long Text Entries in Excel 2007 To break a long text entry into separate lines, position the insertion point in the cell entry or on the Formula bar where you want the new line to start and press Alt + Enter. Excel expands the row containing the cell when it starts a new line. Excel automatically wraps the text in the cell when you press Enter to complete the entry, according to the cell's column width and the position of the line break. Here is another method for dealing with long text entries in Excel 2007:
Or you can right click the text entry and:
Cleaning your Data in Excel If you have copied or imported data into your Excel workbook, you may need to clean it up. An article from Microsoft entitled Top ten ways to clean your data (http://office.microsoft.com/en-us/excel/HA102218401033.aspx) covers features that will help you accomplish this. The basics of cleaning your data include:
Adding Paste Values to the Quick Access Toolbar in Excel 2007 Using the Paste Values option in Excel lets you strip formulas from your data and paste only the resulting values. In Excel 2007 the Quick Access Toolbar is a great way to simplify using this feature. Here's how:
Using the Excel 2007 Camera Tool The Excel 2007 Camera tool lets you take a picture of a range of cells on a worksheet. Before you can use this tool you must first add it to the Quick AccessToolbar on the Ribbon. To add the Camera tool to the Quick Access Toolbar:
To use this tool:
A graphic is created of the range you selected. If you change the original data the picture also changes. You can even copy or move this picture to the clipboard and paste it into Word or PowerPoint if you need to. However, if you copy it into another program it will no longer update when the original is changed. Tracking Revisions in Excel 2007 Excel 2007 tracks worksheet revisions so you can see what has changed on each sheet. To use this feature, do the following:
You can choose to have the changes highlighted on screen or listed on a new sheet. Data Validation in Excel 2007 Data Validation lets you restrict what goes into a cell. For example, if dates being entered must be between a certain range, do this:
If someone tries to enter a date outside this range a warning is displayed that says, "The value you entered is not valid.” Import a Custom Auto Fill List in Excel 2007
Change the Case of Text in Excel To change the case of text in Excel, use one of the following formulas:
For example: 
Display Formulas in Excel 2007 Worksheet
Worksheet Selection in Excel If you have several worksheets in an Excel workbook, it can be time consuming to select the one you want to view. This shortcut can help you speed up the process.
This tip works in Excel 2007 as well as earlier versions. Shade Alternate Rows in Excel 2007 One way to quickly add shading to alternate rows in Excel 2007 is by applying a predefined table style. By default, shading is applied to alternate rows in an Excel 2007 table to make the data easier to read. The alternate row shading will remain accurate even if you add or delete rows.
Using Paste Link in Excel 2007 Most people know how to Cut, Copy and Paste in Excel 2007 but many don’t use Paste Link, which creates a link between the data or formula in the original cell and the destination cell where it’s pasted. Here’s how:
The contents of the original cell now also appear in the destination cell. Workbook Capacities in Excel 2007/2010 If you've upgraded to Excel 2007, or if you are thinking about it, you might be interested in the increased workbook capacities. With Excel 2007 you are no longer limited to 3 sort levels and a worksheet can contain more than a million rows.
Sorting and Filtering in Excel 2007 A big improvement in Excel 2007 is the ability to sort on up to 64 levels instead of the 3 we had available in prior versions. You can also filter data by color or by dates and Excel will display more than 1000 items in the AutoFilter drop-down list. A quick way to sort or filter data in Excel 2007 is to use the shortcut menu:
Add Color to Worksheet Tabs in Excel 2007 It can be useful to color code the tabs of Excel worksheets:
This tip also works in previous versions of Excel. Protect Cells in Excel 2007 You can prevent users from overwriting formulas or inadvertently changing labels or formats by protecting cells in a worksheet. When you protect a worksheet all cells are protected, or locked, and cannot be changed. To allow users to change certain cells in the worksheet you first identify which cells should NOT be locked. Then you protect, or lock, the worksheet. In Excel 2007, follow these steps: Unlock cells that can be changed
Protect the worksheet
Now users can only change the cells that are unlocked. If users try to change any other cells they receive a message saying they must unprotect the cells to modify them. Total Data in an Excel 2007 Table New in Excel 2007, you can now total the data in a Microsoft Office Excel table by displaying a totals row at the end of the table. Here's how:
Working with Large Excel 2007 Worksheets Worksheets that are too large to be displayed on a single screen can cause problems when entering data as well as printing. There are 2 different features that will help you deal successfully with these issues. To enter data into a worksheet that is wider or longer than the available screen area:
To print a worksheet that is wider or longer than the available page size without losing the rows and/or columns required to give meaning to your data:
Calculated Columns in Excel 2007 Tables
Freeze a Formula into its Current Value To freeze a formula into its current value:
Now you can copy or move the value anywhere you need it. This trick works in all versions of Excel, even 2007!
Keyboard Shortcuts in Excel 2007 If you are a fan of keyboard shortcuts you will be happy to know that most of the shortcuts we've used for years work exactly the same in Excel 2007. Here's a list of some of my favorites:
For a complete listing of all Excel 2007 keyboard shortcuts, go to Excel shortcut and function keys (http://office.microsoft.com/en-us/excel/HP100738481033.aspx) published on www.office.microsoft.com where you can find lots of other great resources for learning Microsoft Office. Remove Duplicate Records in Excel 2007 Database
Text to Columns in Excel 2007 This feature can be useful for creating a database using existing information which is improperly formatted. For example, if both first and last names have already been entered into a cell, use text to columns to split names into 2 columns.
Selecting a Range in Excel In Excel, a range is a group of connected cells. When working in Excel you often need to select a range. Here is a simple trick for quickly selecting a range:
The range is selected. This trick works in all versions of Excel. Generating Random Numbers in Excel Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically:
Display Fractions in Excel Worksheet Have you ever wanted to display a fraction in an Excel worksheet? Try this:
Combining Text from Two Cells in Excel In Microsoft Excel you can combine the text in two or more cells into a single text string by using “concatenation”. The result is displayed in the cell where you type the formula. For example, If you have a worksheet that contains the First name in cell A1 and the Last name in cell B1, enter the following expression in cell C1 to create a text string that displays the values of the First and Last names separated by a space: =A1&" "&B1
Be sure to put a space between the double quotes in the expression. Copy this formula down to each row containing the names. This tip works in Excel 2007 and 2010 as well as earlier versions. Calculate Remaining Days in the Year Have you ever wanted to calculate the number of days remaining in the year? If the date is in cell A1, use the following formula: =DATE(YEAR(A1),12,31)-A1
A Gantt chart is a common tool for project planning and keeping track of the status of individual tasks within a project. Go to www.vertex42.com (http://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html) to download a free Gantt chart spreadsheet for creating simple project schedules and timelines. Add Text to Displayed Numerical Values in Excel You can add text to the value of a cell by following these steps:
The format you create will put the word tons after any number you enter into the cell, but Excel will still treat the value as a number, not text Disable Drag and Drop in Excel If you place your mouse pointer on the side of a cell in an Excel worksheet, it changes from an arrow or plus sign into a four-sided arrow. If you click and hold the left mouse button down you can drag and move the cell information. It's easy to move data you don’t want to move. To turn this feature off:
Calculate a Person's Age in Excel The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. This function makes it easy to calculate a person's age. To try this:
The age (in years) will be displayed in cell A2. Close all Open Files in Excel
Using IF Functions in Excel The Excel IF function is a great tool that can be used when the information you want in a cell is conditional. It’s particularly handy if you need to specify two or more different responses for a cell based on specified conditions. The format for this function is: =IF("if the condition stated here is true", "then enter this value", "else enter this value") For example, to see at who passed or failed an exam an IF function could be used. Let’s assume the pass mark is 75 and the marks are in column B, starting from row 5. the formula would look like this: =IF(B5>75, "Pass", "Fail")
Drop-Down Lists Increase Data Accuracy Giving users the option to select data from lists rather than type it manually can help eliminate many data entry errors. But you don't need to create the actual lists to ensure accuracy. After users enter an item once, Excel automatically includes it in a drop-down list of entries that users previously typed in the column. For example, to access the list of vendors previously typed in the Vendors column of an Orders spreadsheet, follow these steps:
Of course, if the automatic completion feature is turned on, data will be entered automatically when you begin typing a column entry for the second time! To accept the proposed entry, press TAB or ENTER. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries. To turn automatic completion of cell entries on or off:
Define Constant Values in Excel Use the Name tool to define a constant value, such as a tax rate, that you frequently use in Excel formulas. Go to Insert | Name | Define and type a name—TaxRate, for example. In the Refers to area, type the constant value and click on OK. For a 9 percent tax rate, you would enter 0.09. For example, enter the formula =5000*TaxRate and Excel will use the constant value defined for TaxRate and return 450. Linking Cells to a Textbox or Object in Microsoft Excel
Insert Time/Date in Excel or Access Here are a few keyboard shortcuts you can use to insert the current time and date in a Microsoft Access table or Microsoft Excel spreadsheet.
In Access, this keyboard shortcut only works if you are entering data in the Datasheet or Form view. Editor's Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions. To learn how to do this, search for Insert the current date and time in a cell in Excel Help and then click Insert a date or time whose value is updated. Shortcuts for Using Names in Formulas Next time you'd like to add a name to a formula you're writing:
Quickly Sum Any Row or Column of Data
What you may not know is that you can get the same job done without so much as a glance at the mouse:
Turn a Date into a Weekday
Create Instant Range Names You've got a huge table, and you want to turn all the row and column labels into names. Big job, right? Wrong again, financial-statement breath. If your cursor is ANYWHERE WITHIN THE TABLE, you can get the whole job done with three keystrokes:
Your names have been created. Using Names in Formulas If you've got names in your worksheet and you want to write formulas that refer to these names, try this:
Not only have you spared yourself the digital (in both senses of the word) pain of typing the names, but you've also eliminated the possibility of misspelling the name. Help Entering Formulas If you want help entering a formula --one that includes a function, that is--try this:
Excel displays the function's arguments RIGHT THERE IN THE CELL (and in the Formula bar); all you have to do is replace the function arguments with cell addresses. Autofill AutoFill the last day of each month--even though they're not the same number of days apart.
Excel understands that you want the last days of each month (including leap days, of course!). Moving Around an Excel Worksheet Today, we give you the handy Go To method. Suppose you want to select the range A1:Z52:
The troublesome large range is selected without scrolling. Copying Selected Characters "If I have a column of text in which each cell contains eight characters, how might I create a second column in which each cell contains only the first five of these characters? Is this possible?" Yes. Suppose the column containing the eight-character cells is column A (starting in cell A1), and you want the corresponding first-five-character entries in column B (starting in cell B1). Try this:
Name a Number You may be familiar with using Excel's Name feature. This feature lets you name a cell (or range of cells) so that you can refer to the cell (or range) by name rather than the more complex cell address in formulas. But the Name feature has another use: You can use it to assign a name to a number that's NOT EVEN ENTERED IN YOUR WORKSHEET. Suppose, for example, that a particular worksheet requires you to regularly multiply numbers by the number .267. You can assign a name to the number .267--and use the name in your formulas--as follows:
Now you can use this multiplier in a formula. For example, suppose you want to multiply a value in the cell A1 by this named constant:
Excel multiplies the contents of A1 by .267. Using Goal Seek You build a sales forecast in Excel, and the totals look SO good you decide to chart them. Only problem is that once you chart them, you wish they looked a little better. Our advice: Change the chart! Or, more precisely, use the chart to initiate a goal-seek operation, as follows:
Excel "raises the bar" on your chart and adjusts the selected cell accordingly. SmartFill By now you all know about Excel's SmartFill feature, which lets you quickly fill a range with successive numbers, dates, and so on, simply by dragging two existing dates. But what you may not know is just how smart the feature is. For example, if you want to fill Row 1 with the last day of each month, just do as follows:
Excel fills each cell you drag over with the last day of each successive month. You can even use the same technique to fill every other day, every third day, every 10 minutes, every three months, and so on. Go ahead, enjoy this new and exciting knowledge! Tracking Revisions Excel tracks worksheet revisions so you can have some idea of what has been done to each sheet. To see these tracks, do the following:
You can choose to have the changes highlighted on screen or listed on a new sheet. Working with Multiple Worksheets Let's hope you never have to wrestle with complicated Excel documents. You know, the ones with lots and lots of worksheets. But if you do, it's nice to know that you can see what's in that document at a glance. To see a list of all that's in the document, do the following:
| Page Yüklə 156,66 Kb. Dostları ilə paylaş:
|
