Tag Archives: Excel

Plotting Empty Cells

I received a call from my daughter yesterday. She was totally frustrated as she was trying to plot data for a class project that contained a lot of empty cells. I told her how to fix it and then learned that these suggestions would not work on a MAC. As I helped her figure how to do it, I tripped across a lot of information on plotting empty cells and realized that a lot of people struggled with this. So, of course, here is a post on how to handle plotting data when some of the data is missing.

Here is a simple example. Below is twelve months data on beverage sales. Almond milk sales did not begin until June. Soy milk proved unpopular and they stopped selling it in May. The company introduced goat milk in January and then decided it needed more work and reintroduced it in November.

Let’s start with looking at the chart. Right off the bat, you can see there is something wrong with the top gray line for organic milk as there is a break in the line. Cider looks to be starting in March even though there is January data. January goat sales do not even display on the chart.

First, sometimes people want to add zeroes. Lets’ see what it looks like if I do that for February cider sales.

Well, now the January sales show but look at that plunge.  Adding zeroes is not recommended. Let’s see what alternatives there are.

In this example, below I have replaced the 0 with #N/A.

As you can see, it smoothed out without that excessive plunge where it was trying to display a zero.

Let’s try the same thing for the goat milk sales line.

Hmm.. not as perfect as it look as though. Goat milk was sold all year but better than what it was. Also, let’s face it, no one wants a lot of error values in their spreadsheets.

Another alternative that is the best, I think, is just show the empty cells as gaps or connect data points with a line.

To do this, right-click on the data in your chart and select Data…

Click on Hidden and Empty Cells.

If you look at the goat milk, when the cells are blank and I use Connect data points with line, it resembles the #N/A version but at least I do not have #N/A errors throughout my spreadsheet.

So, really you have a choice between showing gaps or connecting the lines.

I was reading Jon Peltier’s blog and he mentioned that Microsoft had updated the Hidden and Empty Cell Settings to handle #N/A. So, of course I immediately updated my Excel version and the new update suddenly included the Show #N/A as an empty cell. If you do not see that option, then you either have an earlier version of Excel or you need to update.

So, to finish this off and see how it looked, I put #N/A in all the empty cells, and then selected Connect Data points and put a checkmark in Show #N/A as an empty cell.

The results are below.

So, if you do have #N/As in cells, that option is a nice workaround.

Jon Peltier goes into much more detail and shows how the different choices look in a variety of chart formats so if you are looking for more information on this, I recommend you stop by Peltier Tech Blog.

And for you MAC users, if you right-click and Select Data…, you should see a similar dialog box.

Hopefully, my daughter’s project turned out well. For more information on computer software tips and tricks, see the full catalog of self-study CPE products.

Go To Special

The Special Uses of Go To Special in Excel

Hidden on the Home ribbon in Microsoft Excel, within the Find & Select Button, is an Excel feature that has some very special and generally unknown uses for the CPA.  The feature is Go To Special.  It’s the fourth option on the list when the Find & Select button is clicked.
Go To Special

More…

Only Copy Cells with Data

A Method to Copy Only Cells with 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?
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.jfog-1a

 

As an example, assume that the following data in in Excel and you want it in one column for analysis:
jfig2

Let’s copy only the cells that are not blank from B1:B7 to column A, under A7.
Select cells B1:B7
Click Find & Select and choose GoTo Special.

This menu will appear.

go to special dialog box

 

Select the radio button next to Constants and press OK.

 

Notice how only the cells with data are now shaded.  Hover over any of the three shaded cells and right click.  Select Copy.  Then, select A8, right click and select Paste.

Only the non-blank cells are now pasted into column A.

This technique can save quite a bit of time if you’re working with a large amount of data that requires manipulation into one column.  It also works with any data that needs to be moved and contains unneeded blanks.

Password Protect an Excel Workbook

How to password protect an Excel workbook

Everything these days seems to require a password.. and of course everyone has different specifications. I would love to hear from people on how they safeguard their passwords. Send me an email with your method- send to “info@cpaselfstudy.com”

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.

1. Click on File
2. Click Info
3. Select Protect Workbook

steps to protect Excel spreadsheet
 

 

 

 

 

 

4. Click Encrypt with Password

password protect dialog box in excel
Once you have selected a password, you’ll be asked to re-enter it.
steps for encrypting Excel worksheet

 

 

 

Beware! If you lose the password, it cannot be recovered. Also note that passwords are case sensitive.
When you save the workbook, the password will be required to open it. Keep in mind that there is no way of recovering a lost password in Excel.

This feature not only password protects the workbook. It also uses AES 128 bit encryption which makes the file more secure.
Passwords should be strong. It is recommended that they be at least 7 characters long, contain both uppercase and lowercase letters as well as numbers and symbols.

  • ” I was able to get a head start on my credit losses research through your CPE self study this past weekend.  Excellent quality format – so much better than some of the other resources I’ve been using lately.”  Dec 12, 2018

    - Andrea Ll.

  • “always love your site and prompt customer service !! ”  Dec. 2017

    - Thank you :)

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper