• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Interactive Personal Finance Dashboard Problem|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Interactive Personal Finance Dashboard Problem|Dashboards & Charts|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsInteractive Personal Finance Dashbo…
sp_PrintTopic sp_TopicIcon
Interactive Personal Finance Dashboard Problem
Avatar
Matthew Furnell

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 9, 2022
sp_UserOfflineSmall Offline
1
March 9, 2022 - 1:10 am
sp_Permalink sp_Print

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

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 218
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
March 9, 2022 - 1:52 am
sp_Permalink sp_Print

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.

Avatar
Matthew Furnell

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 9, 2022
sp_UserOfflineSmall Offline
3
March 9, 2022 - 2:26 am
sp_Permalink sp_Print

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. 

Expense-Pivot-Table-Fields.PNGImage Enlarger

Expense-Pivot-Table.PNGImage Enlarger
Pivot-Table-Grouping.PNGImage Enlarger
Transaction-Worksheet.PNGImage Enlarger

Matty

sp_PlupAttachments Attachments
  • sp_PlupImage Expense-Pivot-Table-Fields.PNG (25 KB)
  • sp_PlupImage Expense-Pivot-Table.PNG (14 KB)
  • sp_PlupImage Pivot-Table-Grouping.PNG (16 KB)
  • sp_PlupImage Transaction-Worksheet.PNG (35 KB)
Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 218
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
4
March 10, 2022 - 1:43 am
sp_Permalink sp_Print

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.

Avatar
Matthew Furnell

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 9, 2022
sp_UserOfflineSmall Offline
5
March 11, 2022 - 2:10 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 11, 2022 - 10:01 am
sp_Permalink sp_Print

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

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
7
September 1, 2022 - 2:25 am
sp_Permalink sp_Print

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).

  Screenshot-2022-08-31-091013.jpgImage Enlarger

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.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-08-31-091013.jpg (33 KB)
Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
8
September 1, 2022 - 2:44 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
September 1, 2022 - 10:15 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Misael Gutierrez Sr.
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.