Advanced Excel Zoom-In Pivot Table Timeline Chart: Raw & Uncut

Sharing buttons:

hi everyone Jordan goldmine here I want

to show you how I built this awesome

chart like take a look at this okay I

got the total tornadoes per month over a

full 45 year time series that's this

down here and this is real data ladies

and gentlemen so if I click this and I

go to select data you can see it comes

from this back end we got the year got

the month we got the date which is a

year and the month combined and then we

got the value for that I actually used

in another video I used power query to

make this but we're gonna skip that

let's just assume it's already pre-made

because it's an example alright so

here's what I did we got that data right

there I created a pivot table out of

that data data that has just the date

and the values then to get the timeline

I assigned a time line to it and then I

signed this table or excuse me this

chart is actually a pivot chart so as I

update this timeline it will update this

chart because it's just gonna I just

have the dates here and the values over

on the right side so it's just gonna you

know increase or squish as the case may

be then what I do is I use some

mathematical formulas you know math

Excel formulas to find the smallest date

and then the greatest date right and

then I report that back here and then I

create these two extra series here

that's gonna test if the values are

between that and those two extra series

are gonna inform this little baby chart

this long boy down here right so as I

change stuff over here as this changes

it's gonna actually change the minimum

maximum date which we then can report

back on this chart pretty cool

watch this I'm gonna build it no VBA

just using pivot tables and formulas and

it's gonna be awesome you want to copy

this chart to use in your work please do

go out and use it tell me how you use it

love to hear it okay go ahead go ahead

and check out the link down below in the

description because that's gonna give

you what you need all right so let's

build this ourselves

I'm over here I've created the example

data I've already done this for you so

you can go ahead and start here if

you're following along what do I need to

do well I'm gonna go here I'm going to

click insert

we're gonna click pivot table we're

gonna do an existing worksheet at least

I am you know do whatever you want on

yours right so existing worksheet will

just drop it right here

okay so I've dropped it right there I

have the date value like that

Oh Excel look look excels trying to be

power bi with this dislike date

hierarchy so I'm gonna right click and

go to ungroup

come on Excel know what you're good at

okay let me draw I'm gonna take this

value here this is like the tornado

values I'll drop it here it's gonna give

me the sum doesn't matter if it's the

sum of the average as long as it's

numeric right as long as it's a its

numeric because it's not compounding

anything there's no aggregation coming

going on so it's effectively just

reporting it back to me once I've done

that I can go to insert here we will hit

a line chart like this right here so

I've done a line chart and right now

it's just showing me everything that's

fine let's uh let's make this

better-looking than it is so that I'm

not like bothered by it as I keep going

along so I'm gonna click these here I'll

hit delete drop these here hide all of

these let's get rid of the total I don't

what do we need the total for okay well

click that there the other thing I like

to do you'll see me do this a lot is I

usually format the plot area with a line

and then I'll hit the chart area like

that and I'd hit no line and then we'll

take off grid lines because we don't

need grid lines anymore okay took them

off like this and cool there we go so

then what I'm gonna do is I'm gonna

click insert we're gonna click timeline

here you see it picked up the date if

you haven't used time lines before whoo

you are missing out ok so a time line

allows us to slice on time one weird

thing about time lines is it always

starts over on the right with all this

like space but watch what happens I'm

gonna start slicing here and you see

it's actually giving me kind of the

zoomed in version of what's over here

and if I go over here two months and I

don't want to deal with months because I

want to look at something bigger let's

look at quarters like that so look this

mechanism working per

look how quick it was to make something

like that okay so what do we want to do

next well maybe what we want to do next

is make our bigger long boy chart which

is gonna report to us you know

everything in total so to do that what

we'll do is I'm gonna highlight these

two C and D I'll hit ctrl shift down and

I'm just gonna click insert here and

watch this is just gonna insert a line

chart with everything I want on it right

so it's gonna give me this is gonna be

the axes right here and then this will

be the values so let me hit control X

and I'm just gonna hit control up to get

myself back to the top like that okay so

let's fix this one up a little bit I'm

gonna get rid of these I'm gonna put in

a border right so let's go to format and

you know what happens when the chart

works out right it gets stacked I can't

believe I just said that okay so here's

shape outline I'm gonna take that off

and let's make this here let's make this

outline itself kind of a little less I

think I used purple in my original

example we're gonna make it like that

and let's make it thinner shape outline

now what wouldn't it be great if there's

a way to edit oops wrong one edit like a

chart you do in excel but like kind of

like how you do it in power query if

someone out there knows how to make that

and the love of God please make it

already okay so we have here we have the

the long boy chart that we're gonna deal

with and then over here I'm gonna do min

date max date max value okay so if we

want to get the minimum I'll type in

equals minimum here go to our example

dashboard all I do is select a row a

like that or column a excuse me I'll

select column a that's gonna pull back

the minimum date because that's what's

in column a well just format and then

I'll take this here ctrl C I just want

to get the max equals ctrl V I'm gonna

replace that with a max like that and

we'll just use the format painter and a

reformat that and then if I want to get

the max value which is going to be in

this table here

grab this like so okay so I got the max

value of 399 this is called table 3 I'm

gonna let that one slide because I'm

building it and you don't know this but

I've done this like in 4 takes now and

I'm at a point where I'm just I want to

just do one take and and go and not deal

with editing a million different things

they have so much going on alright so I

don't like that it's called table 3 you

should change the name of a table please

follow my advice highlight series

we're moving on highlight series value

and then we're gonna call this like

highlight I like to think of it as a

background okay so here's how we're

gonna do this I'll do a trusty if and if

this date here is greater than or equal

to the midday and ooh that could've been

a major mistake

we're gonna hit f4 on that got a lock

that guy in all right if this is less

than or equal to the max date f4 Locker

in close that if it's true we're gonna

have it just replace the value if not

we're gonna have it generate an n/a

error we do that because if we want it

to be plotted as I'll show you right

here I'm gonna just highlight this whole

thing and hit ctrl C if we want it to be

plotted it won't plot at A's so I hit

control V boom look at that and how did

that work well if we scroll down you see

that when the value is greater than or

equal to the time line selected range

it's gonna report the value and if we

put we just pasted it on top it's just

another you know it's just another

series pasted on top now let's get that

background series so what I'm gonna do

here is I want to test if this is an n/a

so if it's true if it is an n/a we don't

want it to do anything so I'll just type

in is n/a here so if it's it's gonna

either return a true or false if it is

an n/a we don't want to do anything so

actually what I'm gonna do is I'm gonna

take it not true so if it becomes a

false that's you know trues and falses

and Excel or ones and zeroes and just

multiply it by this max value here hit

f4 I'll hit enter so it's gonna be


zero zero until we get to the 399 so

we're using the max value in this case

it could just be an arbitrarily large

value but the reason we're using the max

value of the series is that we don't

ever want that background to be smaller

than the max value so that just sort of

keeps it visually if we ever do reach

that 399 and we'll never go over so I'm

gonna take this whole thing so whole

column I'll highlight it ctrl C let's go

back to our chart here I'll click on it

and ctrl V now you see creates this kind

of ugly looking line chart so what we're

gonna do is we're gonna right click and

go to change series type and under the

highlight background I'm gonna make that

a clustered column like that I'll hit OK

now you see it's a whole bunch of ugly

lines but if I right-click it and go to

gap width and we make that a zero it's

gonna fill in so look the rest of this

is formatting as you can see this isn't

I would say the most beautiful looking

set of colors let's see let's see if we

can fix it up just like a little bit

yeah why not that looks good

okay so once I have that here I can hold

this I'll hit control C and then we're

gonna go back over here and drop her in

and then what I could do is I could just

sort of stretch this out right so if you

want to build this on your own it

doesn't require a whole lot you just

need these components I'll let you two

do it any sort of styling that you want

I'll leave that to you but look how easy

that was and we did it so quickly and

once you've built it once it's like plug

and play with the data okay

so if you really like this would love to

hear it in the comments something else I

have for you okay so you heard me

complaining my life is very busy right

now and that's a good thing we like that

right it's good to be busy and I still

need to put out content the process of

editing this content it's really just

it's overwhelming for both Rick and I

for the things that we have going on so

for now I have we have improved our

equipment greatly I'm in a new

microphone and we got a new camera I

just got a green screen I got just got

some lights so for now for videos like

this I'm not gonna edit them what I want

to know from you is are you okay with

this quality like

I want to make sure that you get value

but sometimes like if I don't have time

that week I just want to go on and I

want to stream and record something

because I think that the value of what I

can give it to the community is actually

greater than the time it takes to edit a

video and make it like perfect and

whatever and you know Who am I really

even competing with like every all the

other Excel people on YouTube are my

friends so you know I kind of want to

own this sort of style but at the same

time I want to make it valuable for you

so this is really your chance to have a

say in what happens at excel TV and also

get this if you want it to help me edit

videos if you had an expertise in that

and could do that and you know maybe

even want to make some videos of your

own yo just let me know I mean I want

this to be as much your community as it

is um you know anything else so with

that thank you for listening to me keep

on Excel and check out our other videos

please let me know what you think in the

comments and make sure to download this

file until next time again keep on Excel