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).
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).
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:
- 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!
- 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 🙂
Clio44's Blog 