Microsoft Excel Top Tips

    

613-749-4695 / 514-257-0734
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

5 Useful Excel Tips

The calculator

You don't need complex Excel functions and formulas to start analysing data in Excel. Here is a tip that 75% of Excel users ignore and that you will be using daily from now on.

Select two or more cells with numbers in them and take a look at the bottom of your screen (the status bar), you will see . It is the sum of the selected cells. If you right click on or anywhere on the status bar you will see this:

 

you can select this functionality to show the sum, the count, the average.... or nothing at all. Remember that if you select only cells with text in them and that the calculator is set on "Sum" nothing will appear in the status bar.

See more on the calculator in section 3A

Open a new workbook and select cell C1 in the first worksheet. Go to the menu bar and select "Data/Validation" the following window appears:

In the "Allow" text box select "List" and the window changes to this:

In the "Source:" text box write YES,NO separated by a comma and click on "OK". You now have a drop-down list from which you can select either "YES" or "NO. This is very useful when you create a questionnaire in Excel and you want to user to answer exclusively by "YES" or "NO".

See more on drop-down lists in section 1B

Navigating from Sheet to Sheet

If there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen, you then left click on one of the arrows   to scroll right and left and find your worksheet. INSTEAD right click on the set of arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select   in the list and choose your worksheet from hundreds.

See more general Excel Tips in section 1A

One screen two views

Sometimes you want to compare data from a Word document or from some website on the Internet with what you have in Excel. You might just want to look at both documents on the same screen or copy/paste from one to the other.

To do so open Excel and the other document (with Word or you browser).  Go to the "Start" bar at the bottom of the screen. Right click on the empty space (where we have added asterisks):

and a special menu comes up:

Select "Tile Windows Vertically" and you see both documents at the same time.

See more on Excel on Screen in section 3B

Let's say that you want to attract attention to certain data in your reports or databases. You would like that when the value of a cell is greater then $50 the font turns blue and the background color changes to green. You need "Conditional Formatting".

Select  the cell or the cells where you want this to happen go to the menu bar Format/Conditional Formatting and the following window appears (may vary depending on the Excel version that you use).

Excel Conditional Formatting

Select "greater than" in the second text box, enter 50 in the third text box and then click on the "Format" button. Choose blue as color for the font and select the "Patterns" tab and select the color green. Now if you write any number greater than 50 in one of the formatted cells the color of the font and of the background will change. If the value is smaller than 50 the default format is selected.

When you don't want zero values to appear in a worksheet, you go to "Tools/Options/General" and remove the check for "Zero Values" but if you want not to see only the zero values within the table and not the total cells, select the interior of the table and use a conditional format that says "Cell value"/"Equal to"/0 and then select the color white for the font.

With more and more reports being read on screen the Excel conditional formatting is an interesting tool.

See more on conditional formatting in chapter 7A

CTRL, ALT and F Shortcuts Keys

Shift/CTRL/Arrow Down will select all the cells from where you are in the column to the last non-empty cell of the column

See more on Excel Shortcut Keys in section 1C

  Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695 / 514-257-0734
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Microsoft Excel Top Tips