Log In
Register
Home
Active Member
March 9, 2022
Offline
Hello Everyone,
Brand new into delving into Excel problems so bear with me please.
I am following the Finance Dashboard video on Youtube. However I have got stuck when producing the pivot table for the first lot of bar charts to show the expenses per month. On my transaction worksheet I have it laid out as per the video with the date columns formatted as dates etc. I then create the pivot table on my analyse worksheet. This is where the problems show themselves.
I select my Expense type as the filter, date as rows and debit as the values. Now as you can see from the pictures for some reason the date column header comes up as "Row Lables" and then that list also doesnt filter down to each month of 2021. I've checked and double checked all my formatting and I've come to a dead end!
When I try to group it just puts all the expenses under 1 group called "Group 1" but it doesn't actually visually do anything with the values.
Bottom line is how can I get all those transactions to filter down to monthly transactions for that pivot table to be formed into a bar chart?
Any info would be appreciated.
Matty
Trusted Members
February 13, 2021
Offline
Row labels is the default pivot style in excel. If you want to change it click anywhere on the pivot table>design>report layout>show in tabular form. That will actually give the column header you have in for your rows. As for everything else, your screen shot did not load. It would be helpful to have the link for the tutorial you are watching and a sample of your spreadsheet to better help us see what is going on.
When attaching to your post click upload and wait for the little tick mark next to the file.
Active Member
March 9, 2022
Offline
Thanks for the quick reply Jessica.
Good shout on the updating the headers, I always assumed it was automatic for some reason.
The tutorial I am watching is; list=PLqJMRpX6sy-2G_iQnaxk1pLLQnhcmPfvs
Ah perfect, I also never released that you had to click upload on here after selecting the file from my desktop.
Matty
Trusted Members
February 13, 2021
Offline
When grouping by the date are you selecting a month grouping? I would check that, if that doesn't work, a sample of your project would be helpful to see your settings. You're "group1" grouping makes me think maybe you tried grouping and forgot to click the grouping option.
Active Member
March 9, 2022
Offline
So I can't seem to find any settings to edit the type of grouping. The pivot table creates another field called "Date2" which links to the "Group1" grouping when I select the column and click group. No matter where I look I can't seem to find any other settings to customise the column.
I have attached the document. If you go to the Analyse worksheet the pivot table causing me a problem is the bottom right one.
July 16, 2010
Offline
Hi Matthew,
Thanks for sharing your file. There is something preventing your date fields being grouped. I'd say the Pivot Cache is somehow corrupted the date field and Excel is not recognising it as a date data type.
Create a new transactions table (copy the original sheet) and build the PivotTables again from this new sheet (delete the original sheet).
When you place the date field in the row labels, right-click on one of the dates > Group. Do this first, before building any other PivotTables. From then on your dates will be grouped accordingly.
Mynda
August 30, 2022
Offline
I've been running into this exact same problem. I figured out the pivot table grouping, as explained in Jessica's first reply. Looks like there's also a default setting in Excel you can change for this, explained here:
https://ifonlyidknownthat.word.....y-default/
Upon digging deeper though, I realized there's something going on like what Mynda talked about (corrupt data).
In the screenshot (excel file attached as well), you'll see the date column of my table. With normal Excel table behavior, shouldn't all dates be grouped into a year? For some reason, I have a bunch of dates that are listed individually (circled in red and more below that). I tired copying the sheet (values only). That didn't work. I made sure the whole column is formatted as a date. I removed any leading/trailing spaces. But the outliers remain. Only solution I've found so far is to go into each cell and re-type the date.
August 30, 2022
Offline
I did some more searching after my previous reply.
This here solved my problem (there are 4 answers; I used the text to columns answer):
https://stackoverflow.com/ques.....ping-dates
Hopefully this helps, in case someone else is having the same problem.
July 16, 2010
Offline
Hi Loren,
Glad you found a solution. The problem you had is different to Matthew's. Your issue was that the dates were incorrectly stored as text, so Excel didn't recognise them as dates. Here are some tutorials that cover fixing dates entered as text for future reference:
https://www.myonlinetraininghu.....atted-text
https://www.myonlinetraininghu.....t-in-excel
Mynda
