Go To Special

My primary use  for Go To Special  has been to select only the visible data in subtotals and pivot tables; however, it has a lot of other uses.
To learn more about this feature,click here to read my guest blogger's entry.

Creating a Gantt Chart in Excel

Cam't afford fancy software for project scheduling? Try using Excel.

The image below is the end result we will achieve.

In this example, we took each activity and provided a sideways bar to visually show when that activity occurred within the total project. 

To accomplish this we will be using some logic functions as well as conditional formatting.

The project example we are going to use is below:

To read the whole article please click here....

Copying Only the Cells that Contain Data

In my last blog entry I talked about using Go To Special to find blank cells. In this entry, written by guest Joe Helstrom, we cover using it to find blank cells as a useful way to only copy cells that contain data.

 Have you ever had a couple of columns of data that you wanted to combine into one column?  What do you do when there are blank spaces in some of the columns of data?  Many will cut and paste the data from one column to the other, then manually delete the rows containing blanks.  This begs the question; Is there an easier way?
Of course there is! One way is to use the GoTo Special feature in Excel.  This feature is located in the Home menu, under Find & Select on the far right hand side of the toolbar. 

Click here for an example of how to do this.

Deleting Blank Sheet Rows

Deleting Blank Sheet Rows

When you import or download data, you frequently end up with a lot of blank rows that are interspersed among the data. You could sort and then delete all the blank rows or you could filter all the non-blanks but, in addition to being time-consuming, sometimes it does not display the data the way you want.

If you select the column that you are interested in and then click on Find & Select and then select  Go To Special .. and  then select Blanks -  you will find that this is a more efficient way. Then select the dropdown arrow beside Delete on the Cell tab and select Delete Sheet Rows.  To see an example of how this works please click here.

PMT function and Amortization Schedules

In this example, we are going to walk through how to do a PMT function and then create an amortization schedule to make sure that the we did the PMT function correctly. The PMT function is used by everyone - personally and professionally.  For example. if you want to figure out what your car payment or your mortgage payment is going to be - you use PMT.

Click here to continue 

Visualize! Use Conditional Formatting's Icon Sets

Today with all the online presentations be it YouTube, Webinars or just an in-house presentation, visual impact has become a lot more important. People only used to just be concerned about colors and images when making PowerPoint slides but in today's environment  you need to consider it for spreadsheets as well.

Conditional formatting is a great feature in itself but today I wanted to focus on  icon sets as some people are not familiar with them or how they work. I am skipping the basics of the icon sets because if you select cells and apply an icon set, the  display is based upon percentages which I personally don't care for. Instead we are going to skip to the more advanced section where everything is a bit more black and white (so to speak)

Since everyone knows my love of lattes and chai, we will use a product line of coffees and teas. We want to emphasize the product lines that are are under budget in terms of their  unit sales. Which spreadsheet would you want to look at? Which has immediate impact? Which quickly identifies the problem products?

Hopefully, you guessed the bottom spreadsheet! To see how to do this, click on this link. and make sure to grab a cup of coffee before you start!

Workbooks - Protection and Encryption

How to password protect and encrypt an Excel workbook

How many passwords do you have? The answer is probably too many. 

Unfortunately, that is the way things are going and you probably need to start considering password protecting  some of your Excel files too. Be careful though - there is no HelpDesk to call if you forget the password!

Below is a guest post from Joe Helstrom on protecting a workbook.

To ensure that unauthorized users do not access a workbook, a workbook can be password protected. Without the password, users cannot access the worksheets within the workbook. It is an effective control as long as the password is limited only to those users with authorized access, is properly safeguarded and is not shared with anyone else. A password is not very effective if it’s on a sticky note attached to your computer monitor.  However, I am sure that none of you do that.

Click here to see the steps needed to password your Excel workbook.

Use Data Validation to check for Duplicate Entries

If you are setting up a spreadsheet for someone else to enter data this is a neat trick. You can use the Data Validation and the CountIF feature to check for duplicate entries and then not allow the user to enter the duplicate data.

To read more, please  click here.