0
Month Notes January Notes February
Net Worth $500 $600
Liabilities $50 $40
Credit Cards $10 $20
Credit Card 1 $5 $10
Credit Cards 2 $5 $10
Savings & Checking $400 $500
Investments $100 $200

I have a google spreadsheet I use for tracking monthly financials that looks something like this. I use a new tab or sheet per year. I was looking to add a Dashboard to the first tab that would display an individual line for each row.

Essentially I want the row and column labels to be the same I have here but translate the numerical values into a line graph.

I have attempted to select all of the cells with the respective data manually. I have tried selecting the ranges - but with my notes columns and there are detailed rows underneath each of the rows displayed here i.e. credit cards have a row each for each card, etc. So the rows and columns in the sheet are not exactly contiguous. I'm pretty sure I'll have to manually click each item. I have also tried selecting a row with the column intended become that row's label and the numeric values in that row as the plotted lines - but that doesn't seem to work very well either. Especially if I try to add more than one row. to display multiple lines.

Something like this:enter image description here

4
  • Hi. It might be just me, but I can't visualise the problem that you have. Would you please edit your question to include a snapshot of a line graph based on the data that you have included. You might have to draw it manually.
    – Tedinoz
    Jul 26, 2021 at 3:23
  • Thanks @Tedinoz, I've updated the question with an illustration. * It's worth noting that in doing the exercise to illustrate what I'm looking for, that I will have a significant range in dollar amounts to cover, from -$400,000.00 to + $1,000,000.00. This will likely flatten anything ranging from $0 to $20,000. So maybe my data is not necessarily a good candidate of this kind of graph. :questioning-shrug: Even So - I believe I could produce a meaningful 'Dashboard' if I can figure out how to do a line or set of lines for the rows I'd like to graph.
    – dwcouch
    Jul 27, 2021 at 16:34
  • 1
    I’ll look more closely later, but just for the moment are these Chart Multiple Discontinuous Values or Create a Google Sheets chart with multiple data ranges with separate key columns helpful. The idea seems to be to create the data source using a separate filter and/or query.
    – Tedinoz
    Jul 27, 2021 at 23:14
  • Thank you @Tedinoz, I think you've set us on the right track. It's clear I'll have to use some sort of filter and or query to group my data points as I want them graphed. Visually speaking I think a stacked bar graph would be of more value. explore a bit and report back.
    – dwcouch
    Jul 29, 2021 at 15:51

1 Answer 1

0

The data needs to be analysed to remover the blank columns and also to summarise the "Credit card" values. When the chart is created, use the Query results as the source data.

The design of the chart is not part of the question involves changing "Setup" and Customize" features.

Query for the source data (refer CellR13 in the screenshot)

=query({query({A1:A8,C1:C8,E1:E8,G1:G8,I1:I8,K1:K8,M1:M8,O1:O8},"select * where NOT Col1 contains 'Credit'");query({A2:A8,C2:C8,E2:E8,G2:G8,I2:I8,K2:K8,M2:M8,O2:O8},"select 'Credit Cards', sum(Col2), sum(Col3),Sum(Col4),Sum(Col5),sum(Col6),Sum(Col7),Sum(Col8) where Col1 contains 'Credit' label sum(Col2) 'January',sum(Col3) 'February',sum(Col4) 'March',sum(Col5) 'April',sum(Col6) 'May',sum(Col7) 'June',sum(Col8) 'July', 'Credit Cards' '' ",0)},"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 where Col1 !=''")

This combines two queries:

  • both queries are based on arrays where only the columns containing data are used.
  • the second query (Credit Cards):
    • sums each column
    • starts the data range at row#2 so that the column headers are not included in the result.

For information:

  • Cell R2 (Query: Credit cards)

  • =query({A2:A8,C2:C8,E2:E8,G2:G8,I2:I8,K2:K8,M2:M8,O2:O8},"select 'Credit Cards', sum(Col2), sum(Col3),Sum(Col4),Sum(Col5),sum(Col6),Sum(Col7),Sum(Col8) where Col1 contains 'Credit' label sum(Col2) 'January',sum(Col3) 'February',sum(Col4) 'March',sum(Col5) 'April',sum(Col6) 'May',sum(Col7) 'June',sum(Col8) 'July', 'Credit Cards' '' ",0)

  • Cell R6 (Query: Non-Credit cards)

  • =query({A1:A8,C1:C8,E1:E8,G1:G8,I1:I8,K1:K8,M1:M8,O1:O8},"select * where NOT Col1 contains 'Credit'")


Sample data and chart

screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.