0

I've got a monthly spend table with almost 800 rows (one row per customer), and I'd like a line graph, with trend, for each customer. Is there a Macro that will help me do this for every line, and place them tidily on a sheet for me to glance at for a quick overview?

I know it's asking a lot, but doing them one-by-one is a huge task.

enter image description here

Improve this question
5
  • 3
    Does this answer your question? What is the quickest way to create a scatter plot per row in a table?
    – cybernetic.nomad
    Mar 29, 2021 at 15:00
  • Thank you, but not entirely. I have done that already so i can see it per customer, but only if i select said customer. I want an quick-view of all charts so i can scan and see which ones are trending negatively, rather than having to select each customer from the drop-down and going through one-by-one.
    – hydrojack
    Mar 29, 2021 at 15:24
  • That many charts would be quite difficult to scan easily and effectively. I would use conditional formatting to highlight customers with a downward trend and then select them to see the detailed chart.
    – cybernetic.nomad
    Mar 29, 2021 at 20:21
  • Unless there is a formatting that i don't know of that will shade them based on an upwards or downwards trend, i'm not sure i can utilise conditional formatting. The spending is very sporadic. Trendlines on a Line Chart work quite well but i can't think of how to transfer that.
    – hydrojack
    Mar 30, 2021 at 8:03
  • Edited answer to include dynamic sparklines, where the source reference is defined as a text reference in the cell itself. Neat way to "program" multiple sparklines.
    – ExcelEverything
    Mar 30, 2021 at 10:46

1 Answer 1

Reset to default
3

How about sparklines? These are little graphs inside a cell, you can set up one sparkline for the first row, and simple copy and paste downwards. Would be much easier to scroll through as well.

enter image description here

You can even move the sparklines to a new sheet, add a customer name column and increase row height to improved visibility. Or stack them in a square?

enter image description here

Quick way to create multiple sparklines:

Sparklines use relative referencing by default. That is, if you create a second sheet with a sparkline in A1 that refers to sheet1!A1:J1, then copying and pasting the sparkline to A2 will automatically refer to sheet1!A2:J2. If you copy and paste sparklines they also automatically become grouped. If you change the format of one, it will change for all. You can ungroup as required. You can also create the sparkline on the same sheet as the data, then cut and paste to where needed.

Dynamic Sparklines (change the source of a sparkline with a formula)

A comment by the OP "sparked" an interesting question. How do you easily stack and arrange sparklines when the data is linear/row-by-row but your sparklines may be i.e. in a grid. The sparklines' source are hardwired in the dialog box and doesn't accept formulas. How do you change the sparkline source programmatically but without VBA? Well, enter the "relative named range". The sparkline source can be a named range, lets call it sparkref. Put your cursor in A1. Click Formulas > Define name, and define sparkref as =INDIRECT(A1) in the Refers To textbox (note A1 not $A$1). Now create a new sparkline in say E1, and type sparkref as the source. In E1 (same cell as sparkline) type in the data range for the sparkline e.g. "Sheet3!A1:J1" as text (without equal sign). Any sparkline defined like this will always take its source data from the range (defined in text) in its own cell. Make the cell font size 1, colour white and position top left. You can now use formulas to i.e. wrap row-by-row data into a 8x8 grid with ="Sheet3!A" & (ROW()+(COLUMN()-1)*8) & ":J" & (ROW()+(COLUMN()-1)*8).

Improve this answer
3
  • Ah, that works wonders! At the very top of my table i've got a big line graph that pulls data from a drop-down i'm using with an index from the table data. Do you know of a way that selecting the cell with a certain Sparkline will bring up the line graph at the top instead of using the dropdown? Also, is there a quick way to create those sparklines on the next sheet or is it another one-by-one job?
    – hydrojack
    Mar 30, 2021 at 8:14
  • Great. Do checkmark the question as answered so others may know its done. As for "selecting" a sparkline, it will required VBA code in the Worksheet_SelectionChange event. I would keep the drop down, then in event (in rough pseudo code) IF (selectedcell is sparkline) then dropdowncell.value = selectedcell.sparkline.source. BUT to be honest I try to avoid messing with SelectionChange events, it can make things slow and mess with the undo chain.
    – ExcelEverything
    Mar 30, 2021 at 8:37
  • On 2nd thought, you might get away without VBA. You can use macroless form controls from the developer tab (simply save as .xlsx and be sure not to add any code). The "Option button" form control can be copied and pasted as many times as you like, and be linked to a single cell. Selecting one option control will automatically unselect any other and update the cell value to the option button #.
    – ExcelEverything
    Mar 30, 2021 at 8:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .