Add Some Spark to Your Excel Spreadsheets

Creating SparklinesHave you and your spreadsheet lost the old fire?  Do you find your eyes wandering?  Rekindle that flame!  Add some “spark” to your spreadsheet using one of Microsoft Excel 2010’s coolest new features, “Sparklines”!

“Sparklines” are kind of like miniature charts that you can fit into a small area, such as a cell.  They are great for showing trends in a data series.  In the spreadsheet below, you see a series of numbers representing recordable safety violations by month.  Some people can extract what they want by looking through long rows of data like those below.  Others, however, prefer something more visual.

Insert Excel Sparklines

Notice the column labeled “Trends” in the picture above.  This is the ideal place for a “Sparkline”.  To insert a “Sparkline” here, click the cell in which you wish to place the “Sparkline”, then click the “Insert” tab on the ribbon toolbar, and click on the type of “Sparkline” you want from the “Sparkline” group.  See the picture below.

Inserting Sparklines

When you choose a “Sparkline”, you will see a dialog box like the one pictured below.  The first field asks you to input the range of cells that contain the data.  You can manually type this in, for example, A1:A12, or you can click the little square at the end of the box.  This little square will minimize the box and allow you to highlight the data range with your mouse.  It really is just a matter of preference.  The second field asks you where you want your “Sparkline” to reside in the sheet.  Note this can be a range and not just a single cell.

Creating Sparklines Window

Once you have inserted the “Sparkline”, you will see options to format it under the “Design” tab, highlighted in green below.  You can choose from a number of styles.  You can even set different colors for markers, such as high points and low points.

Customize the Sparkline

The next time you have to present a report in a spreadsheet, take a moment to insert one of these neat little “Sparklines”.

Speed Up Formatting in Microsoft Office with Format Painter

Format Painter ButtonLay down the canvas!  Put on your painting clothes!  We’re going to do some painting!  Today I would like to show you an easy way to format multiple objects in Microsoft Office using the “Format Painter” tool.  You may have noticed a little paint brush icon that sits at the top of most Microsoft Office applications.  This button is called the “Format Painter”.  Below you will see a screenshot from Excel highlighting the button.  So what does this button do, exactly?  First you have to understand the word “format”.  When you format something in Microsoft Office applications, you are basically changing the way something looks.  In the picture below, you will notice that the spreadsheet has column headers.  The first column header says “First Name”.  It is different from the other headers because it has been formatted to have a bold font, background color, and font color.  It took a few steps to get that header to look that way.  So what if you wanted  the “Last Name” column to look exactly the same?  This is where “Format Painter” saves the day.  First, Click the cell that has the formatting you want.  Now click the “Format Painter” button.  Notice the highlighted cell below and how the mouse changes to a white cross with a paint brush icon beside.

Format Painter Button in Microsoft Excel

Now you click the cell that you want to change and presto! change-o!, you have duplicated the formatting from the selected cell.  See the picture below to verify this.  Once you click the cell that you want to change, the mouse will go back to normal.  You can click and drag with the “Format Painter” button activated, and it will continue to work until you release the mouse.  Once the mouse has been released, “Format Painter” disables.

Selecting Cells with Format Painter

What if you wanted to do multiple  non-contiguous cells?  This is possible.  In the example below, you will see that I was able to format cells that were not contiguous.  To achieve this, you double click the “Format Painter” button.  Doing this enables you to keep on painting until your heart’s content.  Once you are ready to stop formatting, all you have to do is hit the “ESC” key in the top left corner of your keyboard.

Selecting Multiple Cells Using Format Painter

Below, you will see an example of how the “Format Painter” button appears in Microsoft Word.  Notice that the first paragraph has blue font and 1.5 line spacing.

Using Format Painter in Word
In order to duplicate the formatting of the first paragraph in the second, you must highlight the entire first paragraph, click the “Format Painter”, and then highlight the second paragraph.  Why do you have to highlight the entire first paragraph?  If you highlighted a single word in the first paragraph, and then applied the “Format Painter’ to the second paragraph, it would only duplicate the word formatting.  The paragraph formatting would not be duplicated.  Highlighting the entire first paragraph duplicates both the word and the paragraph formatting.  See the picture below.

Formatting Paragraphs in Word Using Format Painter

Using “Format Painter” can make quick work of formatting in Microsoft Office.  Plus, there’s no drippy mess!  :)

Categorizing Contacts In Outlook

Categorize ButtonIf you’re like me, you have hundreds of contacts.  Sometimes trying to sort through all the clutter can be daunting.  Microsoft Outlook offers a great way to categorize these contacts.  Categorizing your contacts enables you to sort them into logical groups.  For instance, you may want to only view contacts that you work with, or you may want to only see family.  It is very easy to do this in Outlook.  In order to categorize we’ll need to open up a contact in Outlook.  In Outlook 2010 there is a colorful square labeled “Categorize” on the top right corner of the ribbon toolbar.  See the picture below.

Categorize Button In Outlook

You will notice that there are several color coded categories listed in the drop down menu.   Some of these categories have default labels on them such as Businessand Holiday.   The cool thing is you can use these, or you can customize them to make sense to you.   Below you will see a picture of the Categorizemenu.   At the bottom of the menu you will see an option called All Categories.   This is where you click to customize this menu.

Categorize Menu

When you click the All Categoriesoption you will see the box below.   Notice I have selected the generically named “Green Category”.   Over on the right side of the window you have the option to rename.   I chose to rename this Geeksjust for fun.

Renaming Categories Window

Once you have customized the categories the way you want them, you can assign them to your contacts and save them.   Now we can view our contacts in categories.   If we go to the Viewtab in Outlook, there is a button in the top left corner that says Change View.   Below you will see what this looks like.   Notice the envelope with the words By Categorywritten underneath.   Click this option to view your contacts by category.

View Menu By Category

Now you can see a list of categories.   There is a sideways triangle beside each category. When clicked they will expand to show you all of the contacts that are listed in that category.   In the picture below you will see where I have expanded the new Geekscategory I created.   You will also notice that each category heading tells you how many items are listed in that category.   Another thing to note is that a contact can be a member of more than one category if you so choose.   For instance, a personal contact may also be a business associate too.     Hopefully you will find this helpful and maybe think of creative uses for the categories in Outlook.

Outlook Contacts Categorized View