Oct
12

Visually analyse data with Conditional Formatting

Categories:

Applying conditional formatting allows you to change the format of the cells in your spreadsheet, depending on certain conditions. For example, you could highlight cells in a particular colour when the numbers in those cells match a certain value. This is set using a type of conditional formatting, “Highlight Cells Rules”.

Types of conditional formatting

Conditional formatting can be found on the Home tab of the ribbon. If you click on the conditional formatting button, you can then choose the type of conditional formatting to apply:

  • Highlight Cells Rules
  • Top/Bottom Rules
  • Data Bars
  • Colour Scales
  • Icon Sets
  • New Rule (create custom rules)

Highlight Cells Rules

Looks for cells containing data (numbers, text or dates) that you want to apply formatting, based on criteria, or can be used to find duplicate values
Numbers – Use the ‘Greater Than’, ‘Less Than’, ‘Between’ or ‘Equal To’ options to set the required values.
Text – Use the ‘Text That Contains’ option to type in required text.
Dates – Use the ‘A Date Occurring’ option to set a date comparison to the current date e.g. Yesterday or Next Week. Note; to set a specific date or an alternative comparison, you will need to set a custom rule (see ‘New Rule’ below)
Duplicates – Use the ‘Duplicate Values’ option to highlight all of the duplicate cell values in the selected range of cells.

Top/Bottom Rules

Looks for highest and lowest values based on an amount, percentage or variance from average.

Data Bars

Applies formatting to all selected cells. The longer the bar, the higher the value.
This example uses the blue coloured bars to visually display the size of the cell values.

Colour Scales

Applies formatting to all selected cells. Certain colours will represent higher values than other colours.
The Green-Yellow-Red option can be used to graphically display the size of the cell values e.g. Red is used to highlight the low values, going up to orange, then yellow, light green the dark green to display the highest values.

Icon Sets

Applies formatting to all selected cells. Each icon set has between 3 and 5 categories, each representing low, middle or high values.
An example is the 3 arrow icon set. The red down arrow representing the lowest values, the orange right arrow for the middle values and the green up arrows for the highest values.

New Rule

You can create customised rules, rather than using the pre-set rules, so you can use different colours/formatting options and also use formulas for your criteria.
Example – using a formula to highlight dates which are more than 1 week ago (where A2 is the first date):

  1. Go to Home tab >Conditional Formatting
  2. Select ‘Use a formula to determine…’
  3. Type: =A2
  4. Click on Format
  5. Select required formatting, then OK

The formula must always start with ‘=’. The cell reference ‘A2′ is the first cell in the selected range. ‘Today()-7′ represents today’s date less 7 days i.e. more than 1 week ago. If you click on the first cell in the range rather than typing the cell reference (in this example ‘A2′), it will insert ‘$’ symbols in the cell reference to read ‘$A$2′. It will only then compare the data in that one cell, rather than comparing each cell in the range.

Learn more about Microsoft Excel >>

Tags: , , ,

Leave comment

May
30

Get control of your mailbox 5: View settings

Categories:

These days we get bombarded with emails. According to research, in 2010, we are each receiving an average of 74 emails per day, 61 of which are legitimate emails, and we are sending an average of 34 emails per day. That’s 756 emails per week or 39,312 per year !!
So, we need to get organised…!
This article is the fifth in a series of 10 articles that will give you tips for helping you keep your mailbox under control when using Microsoft Outlook 2003, 2007 or 2010.

Column settings

You can add and remove columns in your mail folder screen (each email folder can be set individually) so that you can view the information that is relevant to you. For example, if you do not categorise or follow up your emails then you would not need to view these columns. Similarly, if you have more than one email address you may need to have a column that shows which email account the email was sent to.

Add new columns

There are many different columns available to add to your folder view. Each data field in Microsoft Outlook has an associated column header.
To add a new column:
1. Right click on one of the column headers
2. Select ‘Field Chooser’
3. Find required column header and drag the header up to the header area

Remove columns

Columns can be removed so that they are not displayed on the screen. They can be re-added to the view at a later date if required.
To remove a column:
1. Right click on the column header to remove
2. Select ‘Remove this column’

Re-order columns

To re-order the columns, simply drag the column header to the required location

Sort and Group emails

To make it easier to find emails, you can use various sorting and grouping methods. Generally emails are sorted by date received, where the newest email is displayed at the top of the screen. However, you can sort or group emails by any of the column headings displayed.

Sort by column

To sort emails by a specific column, simply click on that column header. Clicking a second time will reverse the sort order.

Group by column

To group emails together within a column:
1. Right click on a column header
2. Select ‘View settings’
3. Click on the ‘Group By’ button
4. Select which column to group by under ‘Group items by’
5. Click OK
Each group has its own heading, which can then be used to display or hide the items under that heading by clicking on the symbol to the left of the heading.

All of the above methods can be used in any area of Microsoft Outlook where a columnar view is used.

Learn more about Microsoft Outlook >>

Tags: ,

Leave comment

May
23

Did you know…? Interesting software stats

Categories:

Did you know…?

  • There are 100 million more PC’s than 10 years ago
  • 91.46% of all computer users have Microsoft Windows
  • An estimated 1 billion users worldwide use Microsoft Office
  • 77% of Mac users use Microsoft Office

So, to keep up with the other 999,999,999 users learn more about Microsoft Office today!

Tags: ,

Leave comment

Nov
22

Get control of your mailbox 4: Use Rules to organise emails

Categories:

These days we get bombarded with emails. According to research, in 2010, we are each receiving an average of 74 emails per day, 61 of which are legitimate emails, and we are sending an average of 34 emails per day. That’s 756 emails per week or 39,312 per year !!

So, we need to get organised…!

This article is the fourth in a series of 10 articles that will give you tips for helping you keep your mailbox under control when using Microsoft Outlook 2003, 2007 or 2010.

Use rules to file emails

Setting up ‘Rules’ in Outlook will automatically perform set functions on your emails. Rules are more commonly used to automatically file emails into folders, but Rules can be used in many different ways to help keep you organised!

Types of rules

Incoming

This is the most common type of Rule. We use these rules to automatically put any emails that are received into existing folders. These rules can be set by who sent the email, or text that is contained in the email / subject, and many other ways.

Outgoing

The Sent Items are often forgotten: left to accumulate and rarely organised. Rules can also be set to automatically organise your emails as they are sent out.

Set rules as emails arrive

As mentioned above, there are many different types of rules, but the most common is to move an email to a particular folder as it is received. If you use rules to organise your emails as they arrive, you should no longer be checking your Inbox for new messages, but your Unread Mail folder. This folder will display any unread emails regardless of which folder they are in.

To set this type of rule:
    1. Go to Home tab > Rules > Manage Rules & Alerts (Pre 2010 users: Tools > Rules & Alerts)
    2. Click on New Rule
    3. Under ‘Start from a blank rule’ select ‘Apply rule on messages I receive’ then Next >
    4. Check the box ‘from people or public group’
    5. In the editing box, click on the ‘people or public group’ link
    6. Select the person from whom the email rule is to be set then Next >
    7. Check the box ‘move it to the specified folder’
    8. In the editing box, clink on the ‘specified’ link
    9. Choose the folder to move the email to, then OK, then Finish
In the future, any emails received from that person will now go into the specified folder.

An alternative, quicker way of setting a basic rule is to right click on an email received from that person and select Create Rule. Here you will see the basic Rule options.

Learn more about Microsoft Outlook >>

Tags: , ,

Leave comment

Oct
19

Get control of your mailbox 3: Sent items

Categories:

These days we get bombarded with emails. According to research, in 2010, we are each receiving an average of 74 emails per day, 61 of which are legitimate emails, and we are sending an average of 34 emails per day. That’s 756 emails per week or 39,312 per year !!

So, we need to get organised…!

This article is the third in a series of 10 articles that will give you tips for helping you keep your mailbox under control when using Microsoft Outlook 2003, 2007 or 2010.

Organising your Sent items

Many people will try to keep on top of their incoming messages: filing them, deleting them, saving and removing attachments, forwarding or replying to them, and so on. However, most people will forget to organise their Sent items. The Sent items folder keeps a record of every email that is sent (remember the average is 34 per day, that’s 12,410 emails a year just in your sent items folder).

Moving sent items to folders

Just like with your incoming emails, your sent items can be organised into folders. Each email can be dragged from the Sent items folder into the folders under your Inbox (See article “Get control of your mailbox 2: Email folders” for more information on creating and using folders).

Removing attachments

Each time you send an email with an attachment, you are duplicating the attachment on your computer. You have the original document sitting in a folder on your computer, and another copy of that document attached to your email. The size of your Sent items folder can very quickly build up from all of the attachments that you are sending. To reduce the size and increase the speed of your computer, it is recommended that you remove attachments from your Sent items.

It would depend on how many or how large the attachments are that you are sending as to how often you should clear out your attachments. It may be monthly, weekly or even daily if you are sending many large attachments.

An attachment can be removed from an email without having to delete the email itself, so you can always retain a copy of the email as a record that an attachment was sent. It is therefore good practice to write something like “Please find attached document…” in the body of your email, as your record.

To remove an attachment:

     1. Open the email
     2. Right click on the attachment
     3. Select ‘Remove’
     4. Close the email and select to save changes

Tip: Sort your Sent items by size and start with the largest emails first to save space even more quickly! Click on the heading ‘Size’ at the top of the Sent items folder to sort.

Learn more about Microsoft Outlook >>

Tags: , ,

Leave comment

Older posts «