How to Create a Custom Table of Contents in Word [Video]

This video tutorial will teach you how to create a custom table of Contents in Word. The first step is to make a custom style via the Styles group in the Home tab. Second, click on the references tab then click custom table of contents. Click options, then remove the 1, 2, and 3 numbers from the built in heading 1 styles. Type a 1 after your custom style. Apply any format styles if necessary.

Download my Word keyboard shortcuts guide here.

Posted in Videos, Word | Tagged | Leave a comment

How to Nest Formulas in Excel, Using the IF, VLOOKUP, and LEN functions. [Video]

This video post will teach demonstrate how to nest formulas in Excel. I share how to use an IF function to determine which table to gather data from using a VLOOKUP. If the region has 2 characters, then lookup the United States data table, otherwise lookup and reference the international table.

Practice on your own with my logical-nested-formulas.xlsx.

Download my Excel keyboard shortcuts guide here.

Posted in Excel, Videos | Tagged | Leave a comment

When to Use Absolute or Mixed Referencing in Excel Formulas [Video]

This video tutorial will teach you the difference between absolute referencing and mixed referencing. When you want to lock down the rows and columns the formula will have a dollar sign in front of the column and row. Example =$A$1.

When you only want to reference a locking of the row number the formula would look like this =A$1.

When you only want to reference a locking of the column the formula would look like this =$A1.

So a mixed reference is part absolute and part relative.

The dollar sign placement is important as it comes before the position (row/column) you want to make absolute or locked.

Practice with my YouTube example by clicking here. Absolute Referencing Mixed Reference YouTube example.

Posted in Excel, Videos | Tagged | 1 Comment

Considering Switching from Handwritten Notes to Digital Notes with Microsoft OneNote?

Microsoft OneNote is a software program that is a family member of  Microsoft Office. There is a paid version and a free version. The paid version is bundled with your Office subscription of Office 365 or the installed desktop version of Office. The free version is included when you create a OneDrive account. OneDrive (formally called SkyDrive) is Microsoft’s online cloud storage for creating, editing, and sharing documents. OneDrive comes in different versions (OneDrive for business and OneDrive for the consumer). Currently the free version OneDrive comes with 15 GB of document storage plus 30 GB for you camera roll to backup smartphone pictures. You can access OneDrive from your smart phones, take a picture and have it automatically saved to your OneDrive account. I love this feature.

Ok, so the first place to start with taking notes with OneNote is to decide if you want to use it on just one computer or across multiple devices. You will need to save the notebook to your OneDrive account if you want it to sync across your phone, tablets, laptops and computers (PCs and Macs).

Below are 2 screenshots. The first one shows you how to create a new OneNote notebook using the free version with OneDrive. The second screenshot shows you how to create a new OneNote notebook with the paid version of the OneNote running from your desktop.

onenote

From the desktop version click File–>New–>OneDrive to have your sync and share with anyone. Select computer if you plan on just using the notebook from your computer only.

onenote desktop version

After you create your first notebook, then start adding sections and pages. I think of this as the digital “trapper keeper”. I remember in middle school I had a trapper keeper and it was one folder with sections inside for my classes. Math, Science, Social Studies, etc. were individual sections is the analogy to having the digital notebook now with OneNote.

OneNote has an awesome search tool that will find your notes quickly. You can also tag content which allows you to quickly find it by searching for tags. OneNote saves your notes automatically as you enter them.

If you have the paid for version of desktop OneNote, you can transfer copies of emails and meeting calendar items from Outlook to OneNote. Open up the Outlook item and click the OneNote button in the Ribbon. It will then prompt you to select a section with the notebook you want. In addition to taking notes, you can record audio, take screen shots, scan images, use pen tools to annotate and have the option to share notes with others.

For other ways to use OneNote check the ideas presented on the OneNote blog here http://blogs.office.com/onenote/

Posted in OneDrive, OneNote | Tagged | Leave a comment

Display Gold Star Icons in Excel with Conditional Formatting

I want to share with you how to add gold stars to cells that meet a condition. In my example below, I want to highlight any cell that is greater than 90 for the customer service grade.

conditonal formatting Excel start

First I select the cells. Then I click the Home tab–>Conditional Formatting–>New Rule. I select Icon Sets from the Format Style drop down and 3 Stars from the Icon Style drop down. Next I change the percentage to number so that it reads Gold Star is when value is  >=90 Number. I select no cell icon from the other icons to block any other number from showing a half star.

gold stars cond formatting excel

The final output is displayed as.

tars conditional formatting excel

Practice yourself with my spreadsheet example by clicking Gold Stars conditional Formatting.xlsx

Posted in Excel | Tagged | Leave a comment

Edit and Save Photos with PowerPoint

I have Photoshop and PowerPoint and both programs can do some really amazing things. Hands down Photoshop is king when it comes to photo editing. However, not everyone has Photoshop so this post will share how you take advantage of PowerPoint’s photo editing tools. Add your photo to a slide by clicking on the Insert tab then Picture. Alternatively, drag a picture onto the slide.

With the photo selected, a Picture Tools–Format tab will appear. If you don’t see this tab then left click the photo. You can remove parts of the background you don’t want. Read my blog entry on how to do this. There is an adjust group where you can easily adjust the color, contrast and brightness and add filters called artistic effects. There are a ton of styles that will alter your image to a new look and feel. Crop and rotate also are helpful tools.

Format Picture Ribbon

Below is my edits after cropping and applying a change in contrast/brightness.

cropped photo philmont

After you finish with the edits, right-click and select “Save as Picture.” Available formats include .jpg, .png, .gif, and .tiff.

ght click and save as picture

Download my PowerPoint keyboard shortcuts cheat sheet.

Posted in PowerPoint | Tagged | Leave a comment

Remove Spaces with the Trim Function in Excel

Extra spaces in Excel cause problems when analyzing the data. That is because in Excel typing a word and a space afterward is different from just typing the word. In the screen shot example below I have  listed “helmets” 4 times but in reality Excel would recognize it  3 times. That is because one instance (cell C10) has a space after helmets. You can’t see the space unless you double-click in the cell and put the cursor at the end.

Space after helmet

The Trim function is the solution to remove extra spaces. The Trim function only has one argument, the text or cell reference you want to remove spaces with. Use the Trim function on a new column and reference the current cell’s row. Then copy the formula down with the AutoFill handle. I like to double-click on what I call the hockey puck (AutoFill) and the formula will copy down. Just be careful to verify it copied down all the way as a blank row or cell can halt it. Well in all the versions except Excel 2013 the hockey puck (AutoFill handle) I’m referring to is black and now in Excel 2013 the Autofill handle is green. Oh well, I’m sure somewhere in the world they use green hockey pucks, right?

 

Trim function C3

 

autofill

Once you have the new calculated column ready, copy and paste values over top of the original list. The screen shot below has paste values from the paste special dialog box. In this example, I would click on cell C3 to paste values. Pasting values removes the formula reference.

copy and paste values
Practice yourself with my trim function.xlsx example.

Posted in Excel | Tagged | 1 Comment