Tricks and Tips in Working with Micorsoft Excel
More people use Microsoft Excel to do data gathering and generate reports that involve numbers. But there are more things that can be done with this program that would help users do work efficiently, as well as organize the data contained in the spreadsheet as well. Here are a couple of cool tricks that you can do with Microsoft Excel:
1. Putting color in your spreadsheet
This is one sure way of stimulating that blank paper, not to mention that your spreadsheets catch the attention of those who will read through them. Excel users can either color their spreadsheet cells, or color the tabs that categorize the spreadsheets.
To color a tab, just right-click on a spreadsheet tab. Go to tab color, and choose the preferred color from the palette that it will display.
To color a cell, just click on the Fill Color icon located on the Excel toolbar. It looks like a tilted paint bucket; it also has a variety of colors from its palette as well.
Conditional Formatting is also a simple way of applying cell shades on your worksheet. This feature is particularly handy in situations where there is a need to indicate a Pass or Fail condition. Just select the range of cells that needs to be formatted, and select Conditional Formatting on the toolbars. From there, the dialog box will display the multiple options available for different conditions. Users also have the control in color selection.
2. Selecting All Data in the Spreadsheet
Since the spreadsheet is infinite, it can be hard to select all the items in it especially if it contains huge amount of data. If there is a need to select all the contents of the spreadsheet, just click on the upper left corner of the spreadsheet (the box before A and 1) and it will highlight the entire spreadsheet, including the untouched ones.
3. Transposing data
This cool trick eliminates the need of re-entering data through Excel's Paste Special function. Supposing you need to switch the content of your rows to columns, or vice-versa - just select the data that you need to transpose and copy them (shortcut keys would be CTRL + C). Right click on the empty cell that you would like to place the data on, and select Paste Special from the options. This will populate a dialog box with multiple options as to what you'd like to paste from the selection. Select Value, then put a check on the Transpose tickbox. Click on OK, and voila! The values from the rows are now listed in columns, or vice versa. No need to type the data that was collected only to change its places.
4. Auto Calculate
The auto calculate feature can be seen at the bottom right side of the Microsoft Excel window. With this feature, there is no need to manually calculate for either the sum or average of the values of the selected cells, nor is there a need for manual counting (especially if you're in the middle of the spreadsheet). It even skips the need for formulas for sum, count, minimum, maximum, or average values.
5. Hiding / Unhiding Columns, Rows, or Tabs
If there are some areas in the spreadsheet that isn't that important but cannot be deleted from the spreadsheet (like the columns where the raw data is placed), Excel has the option to only hide this column/s so that others can't tinker or see it. The same thing can also be done to columns and tabs of each spreadsheet as well. Just select the column, row, or tab that needs to be hidden, right-click on them, and select Hide. To unhide, select the columns, rows, or tabs that are before and after the hidden item, right-click on them and select Unhide. Take note that hidden rows or columns cannot be printed.
6. Know your shortcut keys
Shortcut keys are not there just for show, it also helps in saving time by skipping the multiple menus that you have to click on. Give the mouse, and your hand, a rest by using shortcut keys such as CTRL + C for copy, or CTRL + V for paste.
There are more cool tricks that you can do with Microsoft Excel without the need of extensive knowledge of the application. Knowing these helps users save time in doing their work and speed up productivity.
Subject Matter Websites for MS Excel Add-Ons
- Decision Modeling Add-Ins for Excel
- Excel Password Remover
- Forecast X
- Spinnaker Software Solutions