Ms. Compy Fix-It: Excel Graphs With Strangely Spaced Charts

Hi there!  Ms. Compy Fix-It here, with another remedied Excel “issue.”

My use of quotes is intentional.  In truth, Excel is not at fault (for once), but it can certainly throw a person for a loop!

Have you ever decided to run a pivot chart, but no matter what you do, you can’t get the bars to space themselves evenly?

This happened to me today when I was testing out a dashboard I was making for a manager to use.  It was making me increasingly annoyed until I finally sought the answer from Google.  But it was no use!  My searches were returning results from Excel tips to the suggestion that I’d somehow grouped two worksheets together….. no luck.  I had to figure this one out myself.

Thankfully, the hair-pulling episode was quicker than anticipated, and involved trying to create a new Chart Type.  As soon as I selected Stacked Column instead of the standard Clustered Column, the spacing issue was fixed.  So, that made me ask myself what was different about that type of chart – and I realized the ‘extra spacing’ between the bars in my chart were actually placeholders – for data that just didn’t exist in my testing entries.  Picture this: I had Resources, and Task Statuses.  Excel’s Clustered Column chart type defined specific placeholder for each of the Task Statuses, so while I may have entered a test entry that Anthony’s task was In Progress, and Amber’s task was Completed, I hadn’t entered that Anthony also had a task that was Completed.  So the placeholder for Completed for Anthony was empty, mimicking a spacing issue (see below).

Excel Charts Spacing2
Well, that looks awkward, now doesn’t it?

But once the data fills in, each ‘space’ allotted for a Task Status will fill up, and only when they each have a data point will it look evenly spaced (see below).

Voila!
Voila!

I realized the clustered column wasn’t what I wanted to use anyway (not for a workload assessment!) but I thought I’d better post my findings here in case there’s someone out there getting as frustrated as I was, simply because there is not enough sample data to show the cause as clearly.

*****

Actually, three Excel issues plagued me today.  This one I was able to resolve, but the other two I will ask for your help as part of the greater Excel community out there:

  1. At one point when I was working with my pivot chart, I was no longer able to select the chart itself.  As in, when I clicked inside the empty space within the chart, instead of selecting the chart (enabling me to move it) it selected the cells behind the chart.  I was able to select individual parts of the chart – axis, title, etc. – but not the chart itself….. it simply could not be moved!  This was such a pain, and I hadn’t invested too much time into the file, I just copied and pasted into another new spreadsheet and re-ran the pivot chart and it was fine.  But it was very disturbing – I couldn’t drag and drop the chart to move it out of the way, and I couldn’t even right-click to see options for it!
  2. After I had copied and pasted my data from the first sheet to the new sheet, a table that was pasted included a border that I wanted to remove – but couldn’t.  I would select the data, click No Border, and it would only remove the bottom border, leaving the sides and top in tact.  When viewing the Format Cells menu of the selection, then Border, it only recognized that the bottom border was there – it didn’t recognize the sides and top!  I tried selecting individual cells and removing the border, also to no avail.  I eventually just highlighted the selection, went under the Editing tab, and Clear Formats removed it (and the rest of the formatting which I just had to re-do).  Still perplexing me what this was all about!

So, my dearest reader!  If you happen to know or have a suspicion about what caused either of the two situations above please write me in the comments section.  Or if you have had any similar experience, please let me know – you never know what additional details may lead to solving an Excel mystery!

Respectfully submitted,
Ms. Compy Fix-It  🙂

Advertisements

Ms. Compy Fix-it – Dates Converting to Numbers

Hello again!
Today I encountered a really annoying problem in Excel. In switching between open files, suddenly some of my dates were converted to numbers. September 3rd, 2014, became 41885, and all my columns expanded. I knew I’d hit something by accident, but no amount of Undo would fix it, and even changing the column’s Number Format to Date didn’t make them appear as dates!

Turns out, I had accidentally hit Ctrl + ` instead of Ctrl + Tab to shift between open Excel files. This turns on Show Formulas! So simple, yet it took me a good few minutes to figure it out. So if you’ve searched for this issue and found this post, Ms. Compy Fix-it is here to tell you you’re not alone!

So, if you find your columns unexpectedly expanding, and weird things happening to your data, check to make sure you haven’t accidentally turned on Show Formulas!

PowerPoint Just Wants to Hurt Your Feelings

Hi all, Ms. Compy Fix-It here.

Today, I found a new suggested solution to my PowerPoint constantly crashing.  I had the problem where any editing of embedded charts/graphs using Excel would often cause it to freeze and require the computer to shut down PowerPoint and “try to recover data”.  Sometimes, it recovered all of it; other times, barely any.  I used to have this problem frequently with a computer that ended up having a hardware issue, so I used to blame that.  Then, I got a second-hand laptop to try, and boy, still a lot of crashes.  Finally, I get this, a barely-used HP beaut with big screen and presumably better system.  Well, guess what?

SO!  I figured I couldn’t be the only one, and did some more digging online….. my spec-i-al-i-ty!  (Wow, I just realized that was quoting one of my favourite childhood movies, The Neverending Story….. I heard the professor’s voice saying that, so I tried to spell it out accordingly.  haha I am a true geek.)

Sure enough, I found this site.  Again, thanks to Microsoft Answers, someone suggested getting this HotFix.  So far, so good.  I’ll report back if it’s junk, though  😉

One thing to note: if you do download this fix, make sure that your problem is exactly what is written in their description.  I don’t want you installing unnecessary patches for problems you may not have….. and also, be sure to follow their instructions (including backing up stuff).  I don’t want people to say I didn’t warn them in case it doesn’t work quite right.  I also did a System Restore point, though I don’t know what the difference is between that and a Backup, to be honest.  I just didn’t have any DVDs/time to run the backup, so I thought that was an okay alternative!  haha hey I never said I knew everything about computers….. heck, why do you think I’m Ms. Compy Fix-It?!

Anyways.  One other thing to note: for me, when I installed the fix, I got scared because when I went to my folder to retrieve a file I’d been working on, it was in PowerPoint Viewer!  I thought it did something to my program/deleted it or something, but all you need to do is right-click your file and go to “Open with…..” and it should give you a choice to select the Default Program, and your regular PowerPoint program should be there as an option.  Make sure to check the box, “Always use the selected program to open this kind of file” to ensure all the rest of your PowerPoint documents open as they normally did.

Happy PowerPointing!

Ms. Compy Fix-It: Adding Labels to Scatter (or any) Charts in Excel

Hi all, Ms. Compy Fix-It here, with another gem of a trick to help you.

So, I was working on some scatter plot charts in Excel, and needed to add labels.  As it is, when you click “Show Data Labels” in Excel, it gives you the two pieces of data in the form of coordinates.  Now, maybe that’s helpful for some people, but it is a lot more useful to me to actually describe each point in the form of a variable name.  I knew someone out there must have had the same thought, so I did a quick search which proved I was not alone.

Thanks to this site, I was referred to the program XY Chart Labeler, which did exactly what I was hoping: added labels to my data points.  You even have the option of where to place the labels, which I’m sure will be quite helpful too.

Hope this helps!

— Ms. Compy Fix-It

Solution for “SPSS Error Code 2063 Getting Data”

Hello all.

Just troubleshot (haha) my way out of an SPSS issue, and since the Interwebs couldn’t help me out, I’ll post it here for anyone else who might need it.

I was trying to import an Excel file that was basically a copy and paste from a different file.  So the file itself was pretty new, not like it’d been worked over a lot.  I tried to import it, however, and got the following error message:

GET DATA /TYPE=XLSX  /FILE=*****
/SHEET=name ‘RawData’
/CELLRANGE=full
/READNAMES=on
/ASSUMEDSTRWIDTH=32767.
>Error.  Command name: GET DATA
>(2063) Error reading information for sheet.
>* Sheet Name: “RawData”
>This command not executed.
DATASET NAME DataSet1 WINDOW=FRONT.

The error, as I could figure out, was probably due to me having calculations lower down in my sheet.  I copied and pasted values into a brand new sheet, and that imported just fine.  If you’re having the same issue, try copy and pasting Values only into a new sheet and see if that helps.

The More You Know…..  😉