writing this set of tricks I found
more than 600 relevant
blogs and
sites with the help of
Google. Not all of them are different but some have incredible codes which empowers you to control Excel in very easy way and in full. Some of the codes and tricks are given below, I hope it will help you save your time and increase your productivity.
When I open a workbook Excel asks if I want to update the links. I've looked everywhere, and I can't find any links in my formulas!- I've never known Excel to be wrong about identifying links, so there's an excellent chance your workbook does contain one or more links but they are probably not formula links.
- If you have a chart in your workbook click each data series in the chart and examine the Series formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it and move the chart's data into the current workbook and recreate your chart. If your workbook contains any dialog sheets then select each object in each dialog box and examine the formula bar. If any object contains a reference to another workbook just edit or delete that reference. And if these two approaches don't solve your problem simply follow the following steps...
- Step 1:
Select Edit » Links. The link dialog box will appear.
(In some cases, this command is not available. If you can't select it just skip to step 4.) - Step 2:
Click the Change Source button and change the link to the active file. - Step 3:
Select Insert » Name » Define. Scroll down the list in the Define Name dialog box and examine the Refers to box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of phantom links. - Step 4:
Save your workbook. When you re-open it, Excel won't ask you to update links! Simple!!
How can I make text in a cell display in multiple lines?
When entering text into the cell, press Alt-Enter to insert a line break.
When you do so, Excel will automatically apply text wrapping to the cell.
To re-format existing cells so they sport wrapped text, select the Cells and choose Format » Cells. On the Alignment tab select Wrap text and click OK.
Sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with Visual Basic.
Microsoft has acknowledged some problems with the Excel calculation engine. In order to be assured that all of your formulas have been calculated press Ctrl-Alt-F9 to force a complete recalculation.
Dates and times:
How can I calculate the difference between two dates?
Excel stores dates as serial numbers. The number 1 represents 1st January 1900, the number 2 represents 2nd January 1900 and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date. DATEDIF function which was not documented prior to Excel 2000 returns the difference between two dates expressed in years, months or days.
Excel's DATEDIF function takes three arguments. Its syntax is:
=DATEDIF (start_date, end_date, units)
In the syntax start_date is a date or reference to a date... end_date is a date or reference to a date... and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates.
Acceptable values for the units argument are shown below...
- y returns the number of full years in the period.
- m returns the number of full months in the period.
- d returns the number of full days in the period.
- md returns the number of full days in excess of the last full month.
- ym returns the number of full months in excess of the last full year.
- yd returns the number of full days in excess of the last full year.
For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):
=DATEDIF(A1,B1,"y")
The formula below calculates the number of full months between the two dates:
=DATEDIF(A1,B1,"m")
I have a range of time values but when I try to sum them the total is never greater than 24 hours.
When you add a range that contains time values, Excel ignores the hours that exceed 24. The solution is to use a custom number format. Activate the cell that contains your total time and then choose Format » Cells » Number tab. Choose Custom from the Category list and type [h]:mm into the box labeled Type. Using brackets around the hour portion of the format string tells Excel to display hours that exceed 24 hours.
I have a worksheet that shows total hours and minutes worked along with the hourly pay rate. When I multiply these vales I don't get the result I'm looking for. What's wrong?
Multiply the result by 24 for the number of hours in a day.
If cell A1 contains the number of hours worked (for example, 16:45, for 16 hours and 45 minutes) and cell B1 has the hourly rate then the formula below will calculate the total wages...
=A1*B1*24
Make sure the cell that contains the formula is formatted as a number and not as time.
Is it possible to determine the day of the week for a particular date?
Yes, assume that cell A1 contains a date value. The formula below uses the WEEKDAY function, which returns an integer between 1 and 7 (1 for Sunday, 2 for Monday, and so on).
=WEEKDAY(A1)
If you'd prefer to see words rather than integers please modify the formula as follows:
=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
Another approach is activate the cell that contains your date and then choose Format » Cells » Number tab. Choose Custom from the Category list and type a custom number format string into the box labeled Type. The trick here is to use dddd as part of the format string.
For example, a format string of dddd mmmm d, yyyy will display the date and the day of the week, like this: Thursday November 23, 2000.
I need to determine the difference between two times. If the difference is positive, it works fine. But Excel doesn't want to display negative time values. Why not?
Excel stores dates and times as numeric values, so it should be possible to add or subtract one from the other. The problem occurs if you have a workbook containing only times and no dates. Subtracting one time from another doesn't always work. Negative time values appear as a series of pound signs, even though you've assigned the [h]:mm format to the cells. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date-time combination that falls before this date, which is invalid.
The solution is to use the 1904 date system. Select Tools » Options » Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.
Note: If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.
How can I enter the current date into a cell so it doesn't change from day to day? When I use the NOW function it always shows the current date!
The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a semicolon).
Similarly you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon). Both of these key combinations enter the information as a volatile value and not as a formula.
Chart and printing:
How can I print a workbook's full path in the header or footer? The Page Setup dialog box doesn't seem to offer the option.
Microsoft continues to ignore what must be thousands of requests for this feature. Although Microsoft Word offers this feature but Excel offers no direct way to print a workbook's full path in the header or footer. The only solution is to create a macro. The technique described below works with Excel 97 and later.
Press Alt-F11 to activate the Visual Basic editor.
In the Project window, double-click the project that corresponds to your workbook. The project list will expand to show several objects.
Double-click the item labeled Microsoft Excel Objects and then double-click the object labeled ThisWorkbook.
Enter the following three lines of VBA code into the code module for the ThisWorkbook object (usually in the right pane of the window you're seeing at this point).
Private Sub Workbook_BeforePrint (Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName
End Sub
After inserting the code, press Alt-Q to return to Excel.
This procedure will be executed before you print or preview your workbook. It simply inserts the workbook's path into the left header position. If you prefer to put the path in a different position, substitute any of the following for LeftHeader: CenterHeader, RightHeader, LeftFooter, CenterFooter or RightFooter.
How can I save a chart as a GIF file?
You can save your worksheet as an HTML file and Excel will automatically convert any charts to GIF files. If that seems like overkill you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window and choose Insert » Module to insert a new VBA module. Then type the following four-line procedure into the module...
Sub SaveChartAsGIF ()
Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
End Sub
After the macro is entered, reactivate Excel and click the chart to be saved.
Press Alt-F8 to display the Macro dialog box.
Select the SaveChartAsGIF macro and click Run. The procedure uses the chart's name as the GIF file name, and the file is stored in the same directory as the workbook.
This simple macro does no error checking, so it will generate an error if a chart is not selected or if the workbook has not been saved.
The above information is freely available on the internet. Ayub, Umakant and some friends helped me finding related questions.
Thanks Imageshack fo hosting the above image for free. Please use imageshack for all your website picture hosting.
