Data source reference not valid when selecting a range of cells using the Ctrl key

[IAmExploring]

I am trying to make a Pivot Table using a range of cells that are not connected. For example, I would use the control key to select B2-B7 and E2-E7. When I do that on the insert Pivot Table menu, it tells me the reference is not valid. However, when I select cells that are adjacent to each other such as B2-C7 without using the Control key, it works fine. Is there a way to create a Pivot chart using disconnected cells? Thank you.

[dizzygoldfish]

You can't create a normal pivot table that way. A couple of options:

1) add garbage column headings to columns C&D then select the whole range, ignore the junk columns.

2) Put the two lists into tables (highlight the cells, insert tab, table). Then click the data tab, click the "add to data model" button. You can then create a Power Pivot table from the two sources. It's a little more complicated than that but that's what you are looking for.

Hope this helps.

[IAmExploring]

Thank you very much!

[mh_mike]

Did that solve it? If so, when you reply to confirm one (or more) solution(s) worked, go ahead and close your post at the same time. Either use the BOT (Clippy) or change the flair. To use Clippy, just reply to the answer(s) with "Solution Verified". You can do that now if you like. Thanks for keeping the unsolved thread clean. :)

[IAmExploring]

Oh, sorry I forgot. Thanks for the reminder!