Tips & Tricks‎ > ‎

Excel Tips

Create an Instant Chart

posted Apr 2, 2011, 8:39 AM by Ayush Jain

You can create a chart quickly in Microsoft Excel without having to use the chart button on the toolbar by using these two shortcuts whilst inside a range of data. The chart created is Bar chart which you can change as per your requirement.
 
Here are the shortcuts:-
  1. ALT + F1: This shortcut creates chart with in worksheet.
  2. F11: This shortcut creates chart in new worksheet of same workbook.

Example

Just select the data range and click ALT+F1 to create an Instant Chart

Find the currently active cell

posted Mar 24, 2011, 8:09 AM by Ayush Jain   [ updated Mar 24, 2011, 8:11 AM ]

If you’ve been scrolling around your spreadsheet and you lose your place, you can jump back to the currently active cell by pressing the [Ctrl] + [Backspace] keys.

How to hide formulas in MS Excel worksheets

posted Feb 25, 2011, 11:11 PM by Ayush Jain

This trick will let you hide the formulas & keeping the worksheet usable, by performing the following steps:
  1. Select all cells (either press Ctrl+A or click on the small upper left square on the edge of the worksheet)
  2. Right Click and select Format Cells
  3. In the popup menu that opens, click on the Protection tab (it should be the last one on the menu)
  4. Deselect the small box tagged Locked
  5. Click on OK. Now all your cells can be overwritten, regardless the protection status of the worksheet.
  6. Select only the cells containing formulas that you want to hide (If you need to perform multiple selection, you can press and hold down the Ctrl key, while clicking on each cell you want to select)
  7. Right Click and select Format Cells
  8. In the popup menu that opens, click on the Protection tab
  9. Tick the small box tagged Hidden and the Locked box as well. If you don’t tick the Locked box, other users of your worksheet would be able to overwrite the formula cells, without even knowing that they contain formulas (as they become invisible following this operation).
  10. Click on OK
  11. Go to Review Tab and Select Protect Sheet
  12. If you want, you can input a password for unlocking the worksheet. This will prevent others from unlocking it. If you don’t want to do that, leave the password field blank and press OK.
  13. Now click on one of your cells containing formulas and look at the formula bar. It should be empty, although the formula is still there. The cell would remain locked, but it would be automatically updated when changing the content of its precedents relating to the contained formula.

Hide the Ribbon in Office 2007 / 2010

posted Jan 30, 2011, 7:27 AM by Ayush Jain

Press Control + F1 and ribbon disappears and press again to reappear ribbon. You can also have it disappear so it will come back with a single click on any tab name. To do this, double click a tab name and the ribbon disappears. Single click a tab name and it reappears - click again on the tab or in the document, and it disappears.

Repeat until you're tired of the magic! Double click or Control + F1 to go back to how it is.

Inconsistent date entries

posted Jan 30, 2011, 7:25 AM by Ayush Jain


You need to exercise caution when entering dates by using two digits for the year. When you do so, Excel has some rules that kick in to determine which century to use. And those rules vary, depending on the version of Excel that you use. Two-digit years between 00 and 29 are interpreted as twenty-first century dates, and two-digit years between 30 and 99 are interpreted as twentieth century dates.

For example, if you enter 12/15/28, Excel interprets your entry as December 15, 2028. But if you enter 12/15/30, Excel sees it as December 15, 1930. This is because Windows uses a default boundary year of 2029.

You can keep the default as is, or change it by using the Windows Control Panel (use the spinner in the Calendar area of the Date tab of the Regional and Language Settings Properties dialog box).

Opening Multiple Windows / Comparing two or more sheets

posted Jan 30, 2011, 7:16 AM by Ayush Jain

One of the most frustrating problems for Excel users is viewing all the information they need, especially because it often lives in different worksheets. Here you can know about this simple way to view multiple sheets, and you will love forever.

1. Click on the worksheet you want to view.
2. Choose New Window from the Window group of View Tab.
3. Repeat this process for each of the worksheets you want to display. (Excel opens each sheet in a window that sits on top of the previous one.)
4. To see them all at one time, choose 'Arrange All' from the Window menu.
5. Select Tiled, Horizontal, or Vertical.
6. Select the 'Windows Of Active Workbook' check box.
7. Click OK.

Excel’s leap year bug

posted Jan 30, 2011, 7:07 AM by Ayush Jain   [ updated Jan 30, 2011, 7:12 AM ]

A leap year, which occurs every four years, contains an additional day (February 29). Although the year 1900 was not a leap year, Excel treats it as such. In other words, when you type 2/29/1900 into a cell, Excel does not complain. It interprets this as a valid date and assigns a serial number of 60. If you type 2/29/1901, however, Excel correctly interprets it as a mistake and doesn’t convert it to a date. Rather, it simply makes the cell entry a text string. How can a product used daily by millions of people contain such an obvious bug?

The answer is historical. The original version of Lotus 1-2-3 contained a bug that caused it to consider 1900 as a leap year. When Excel was released some time later, the designers knew of this bug and chose to reproduce it in Excel to maintain compatibility with Lotus worksheet files.

Why does this bug still exist in later versions of Excel? Microsoft asserts that the disadvantages of correcting this bug outweigh the advantages. If the bug were eliminated, it would mess up hundreds of thousands of existing workbooks. In addition, correcting this problem would affect compatibility between Excel and other programs that use dates. As it stands, this bug really causes very few problems because most users do not use dates before March 1, 1900.

Filter by the Selected Cell

posted Jan 30, 2011, 6:52 AM by Ayush Jain

Excel 2007 added the feature to filter table fields by the selected cell.

What does this mean?

Focus on analyzing data instead of defining filter criteria each time.

This quick filter method is ideal for selecting categories, for example: cities, products, subjects, suppliers, etc.

You can filter by:
  • Cell's value
  • Cell's color
  • Cell’s font color
  • Cell's icon
How to…

1. Select the cell on which you want to apply the filter or right click directly on it.

2. Go to: Filter>Filter by Selected Cell’s value

3. Click

1-8 of 8

Comments