Data Analysis using Excel- Database Queries, Filters and Pivot Tables

I have noticed that many people throughout the world have been interested in my training videos on Using Database Queries and Pivot Tables in Microsoft Excel. So this training video, while very helpful for Active Planner customers, is applicable to any Excel user who wants to create database queries and then analyze that data using Filters and Pivot Tables. And as an added bonus, we will go into SQL Management Studio to build and debug our query and I will show you how to create a PIVOT query. Thank you, Doug Leasure

---

Closed Caption:

hello my name is Doug relation want to
thank you for joining me for another
training session
detectives planner actually yeah that's
not totally true what we're going to
cover today is using Microsoft Excel and
doing day-to-day screws and then
analyzing that data using that
smoldering and pivot tables so the
training session I'm doing today is very
generic applies to more than just a
clatter even lower effective amount of
customers can use this this information
as well
the other thing we'll be doing today is
that the end of the session will also go
ahead and the jump into sequel
management studio and take a look at
creating a pivot query and matted
moments
all right and sometimes stuff some good
stuff since Clinton down and get right
into it so i'm going to go ahead and
connect to a remote desktop connection
to another another system won't have
access to much
many many different general ledger
systems lot of different databases and
we use that to you are our training
today
alright so the first of all they do is
going to get into Microsoft Excel
this is version 2007
even though we can do the same
functionality and in two thousand three
seven two thousand seven and twenty ten
all the different versions but i'm going
to use seven countries with all of the
of the typical excel version that our
customers are using all right first
thing you want to do i used to go ahead
and create a database query so we can
explore to choose the menu choice data
and from there are only two
I choose the some other sources and the
source that were using today is going to
be sequel server so I'll choose from
sequel server and that will give us the
opportunity then to have to define the
server the sequel server working - and
the blog and it'll mean so the sequel
server using is is this sequel server
and you will want to be needed work with
your IT folks your database
administrators to to know which house
sequel server to use and what your
credential you need to log on my chase
I'm not using the network logon I've
been given a specific of using an
attachment so we'll use that to log into
this sequel server
ok so one of the signs the conductivity
them to that to that sequel server and
then we'll choose from there the
particular database and one can use a
particular database any particular table
or view and in this case we want to go
to try the data to sheet one please
click OK here now every time we run the
query or refresh the query is going to
ask for for the TCAs word
ok so it's just need protection
again if you really using our or domain
or network username and a permission to
it and we could use the trusted
connection and I won't ask where are our
password is only around refresh
including it's like a password
ok so creating the query initially the
database queries is just that simple of
them going to the to the little wizard
so the magic comes then if we need to
modify the query so we share what that
would look like here in i'm in Excel
2007
ok we have a query that has been
established in connection to the data we
look at the lot of properties so i need
to go to the back of the data the data
tab again and look at our connections
consider the other connection to that
particular view in the database if i
look at the properties called that
particular that particular connection we
can go to the definition here you can
see that it is it was just returning all
of the data file a particular table and
while that's that I might be a great
great great a starting point
a lot of times I find that I need to be
a little more specific
and so what I like to do is go ahead and
switch it over to a sequel query
ok that will allow me too much more
more specifically defined let's see what
I want to return and that's even added
me he needed a where clause to to select
a specific data that I need so that it
on the command is going to be sequel and
here i can type in my select student
sure i could select just all fields from
that particular time that particular
database
ok invited in my case I want to be a
little more specific
ok
and so what I want to do is to guard
against and every tree the piece of data
that I thought I care about and that
data will be the mom
so let me just cancel here let's just
move this time a little bit okay
it returns she valuation call group etc
and what I want to do is to modify that
ever so slightly to return these
specific columns I care about so when
used start over here real quick it was
just take a second and i'm going to i'm
going to them select the column group
the accounts deed on the Skippy getting
getting just return the end of the date
the amount too . and instead of the
fiscal year ends left to do this as the
told you the year function to return
just a year portion of the fiscal year
ended and we'll give that a slight
because the name call this the crystal
year first looking that's from the just
claims have a little bit
they're going to delegate a pub database
and then from the particular Cleary
so I called her two counts d and the
amounts . and then the year of the
school year end and calling that column
fiscal year wonder why you love even the
score pump video is about to begin with
your queries slide you can see ecology
me to command type this sequel
everything
lidar sucks dude directly in them
ok this was a by the way you're changing
the definition of that about a previous
question will see us going to practice
again for the password as it executes
this new clearing returning the data
then to our sheet 1
ok so it's pretty much have simple to
create our query from excel and even to
not to modify the query to be trying to
specific data not only care about you
can you can see that push you to the
lights formats and meeting 2007 above
that I automatically automatically
creates the filtering sports
alright so my first level of analysis
also have the data is to go ahead and
choose the filtering then you need
she also wanted to orders on just be
another budget the actual data which
means that called information and
there's our first level of analysis
going to look at a particular fiscal
years i think it's going to have one so
let's look at in particular .
information rather than sucking
everything can filter to a particular
period and the idea here this is our
first level of analysis
some people are getting back again
additional all of the deal and now let's
extend this capability
ok fairly typical thing we're going to
do what we're looking at
that's the general logic data as we know
that our account number
actually I can't you use some segments
account looking at all natural count and
in this case it's the first segment of
the natural Council of bilocation
departments and product category so we
might want to extend our filtering again
by adding our own long break down so you
can do your very quickly is looking to
move around
filter for the natural account number so
let's take a heading and then you can
use it very simple formula in excel
formula to take our account number and
we will not beginning that had the first
item there in the first account number
of first character sorry account number
to the fourth character
this will represent our our natural
account you can see it is picking off
the first four characters here in the
account number
let's do a very similar looking for tea
location and the apartment had been
using the scene same methodology was
just take the lid off the account number
in this case we're going to begin at the
bus its character and picked off three
more characters that will be our
location and then in a very similar way
if we do the mid of the account number
and beginning in the beginning in the
beans character can call three more
characters so you get a common here and
i will give us our department numbers
you can
what this does is very easily extend our
field French capability
i also need to look at a particular
department head for that department
oh and get this looking out to just be
not just into the vegetated ok very
quickly and easily we got in the raw
data from the database query added our
own peril methodologies for a further
separating words subdividing data and
this is our first level of analysis to
the order quickly needs lead from a
filter that data to look at the specific
information that we care about
ok so the next you want to take a look
at not believe I gotta head and hands
out by data added some additional
breakdowns
let's do this information and Alex use
it to the table to analyze the data and
we have some fun
all right so you can excel time in a
very easy thing to do so by looking at
our information i want to do is to go
ahead and insert a pivot table insert
pivot table
alright and find the fault is going to
look at his current range of information
which is coming far
I'm our query and we're going to do that
to the new fortune
so click ok and we're creating new pivot
table and create a new she and have a
table inside this new sheet
all right so what do we want to put it
well you want to Tibbett let's she start
at the highest level
I want to be able to do some some more
filtering as he did on on the raw data
so we might want to filter the fiscal
year so the reports folder where to
begin with our school year and we might
want to also smelter the ivd the column
group then and then
now in addition that my the soldier the
department had location along the world
information where we want to actually
put here
our our account wonder in the room
the value that we're going to be staying
will be the amount and then we want to
break the town of the columns they're
based on the either the . okay for you
could also do the end date
okay so taking a look at the way this
work I'm going to talk to this to a
particular year and in particular set of
data
this wasn't just a budgeting and then
for a specific department or departments
and comes you can see how quickly easily
and i'm going to use an activity we can
actually very easily analyze it and now
we I don't like the way the you know the
information looks here
I just simply that takes here was a
little funky that's going to be just a
little bit of formatting here and let's
just play it so much
straight as a date and that's just
something to display a lot p month in a
year that will help
alright so very quickly using we be able
to create a query to bring in gator farm
eight remote data source
add some additional filtering their and
analyze do some analysis are using just
simple filtering but then we took it a
step further and created a pivot table
from that information very quickly and
easily and now you can see how we can
use it to the table see the information
and do not format makes it very easy to
see
account numbers and then the your mouth
stands for the various various periods
not quickly and easily how we can we can
do some slicing dicing analysis now .
although information for a particular
and whatever site so why use whether it
be the part of the location
ok the the column group which is the
budget will be natural for a particular
year it's not so easy now to using the
pivot table then to slice and dice I
data
ok that was that goes up our water
wanted to cover what to do next used to
go and do a very some working but rather
than using actors planners published
data
let's go ahead and Moon and take a look
at using specific data from a general
ledger
ok so there's a variety of technology
information the topic available
drop me what I want me know some from
working
look up with a lot of a different in
general ledger information is that a lot
of it returns her store the information
in general ledger com balance
information in a format that is very
similar to what we're seeing here that
is the account number and the the
balance information for various this
cold years and periods in a format that
stuff so this where the beach
each row is is the information for a
particular color first particular year
and in . and it was so late autumn is
this row formats
so what we're seeing here so the entire
doing in query against the a
particularly an alleged is to use our
last 500 on data for our for our sample
today we're going to go to sheet2 we're
going to create a new query
ok remember the steps we go to data and
we are we trading data from another
source and that source of the sequel
server will make sure the service sequel
server name and then a username and
password
ok we'll select the data base that
contains our I didn't want to query and
then we'll choose a particular
particular table
actually the information we want to
start with is actually going to be any
account history that's the table that
contains the balance information so bad
and Amanda choose that as a table and we
do our finish we can put here in sheet2
and not for the password and execute the
query
alright so this is returned to the
information and for the from that
particular table and now want to go
ahead and modify the query in collecting
and our own our own theory
not this particular general ledger uses
president storing the account number in
the table
it actually stores an account keys we
have to join to another table to see the
full account number not a big deal that
you can do that as a as we create a
query right let's go back into the data
and then under our connection you can
see now that we actually have two
connections in in this excel file one
for the I want to be active Plantard
published beta and then we just created
now then to our about her own master
hundred GW
so this is what we want to modify always
click on the properties they're due to
the definition and rather than doing the
query against entire table
we're going to tell them you want to do
a what you want to do a on our own
sequence
I'm going back there again solution see
the two veins here
right again it's going to be a sequel
command that we're going to be an
engineer with you to take them now are
my sequel statement
so we are going to select the the GL
account
ki nameless field account and
you want the school year
that's cool . but we don't care about
the beginning balance we do you want the
credit and debit actually I think the
best thing to do is let's looking to
have a hands-on actually do the pivot
allowance - the credit allowance
we do that here you call that
let's change are typically what we want
to look at in our results of the day
together we're doing the analysis and
really done then that change makes a lot
more sense
so just just Matthew deadlines credit
and call that net change and it should
be the field that we need to do our
analysis going
this isn't some this particular
particular duties
ok so your counter has the account
number to school year fiscal period and
then 7 - credible with Matthew and call
them that change from this to you
now yes we know that we're changing
ok so we have our year school . account
number and that change
now we need to go back and change the
account key to pick up the real account
number so let's go ahead and modify that
one more time and we're going to do here
is to go ahead and join them to the
table that contains our account number
so will join to the the GL count table
yeah
yeah
cancel the GL account
you know , i'm going to join him and
then this is the this will be then what
you want to join honest news matching
this year the GL account key to the two
GL account canyon in each table some ok
so our history
we're gonna give it a failure some of h
and r , number an alias with a so we're
going on is the each . GL account key
equal to the e dot DL house key
and you see you ok
password
ok and so the mean value hundred things
we gave it wrong wrong table name so i
wanted because I wanted to show you this
this message so you can see that i'm
doing the query we can you can get a
little bit of of validation hear me home
from the to actually execute the query
but not a lot of help
we only go wrong but we don't we don't
get a lot of helping you know what it
should be and user want to take a quick
second and show you that in didn't you
if you have access to the Microsoft is
equal management studio and then get a
lot of a lot of helps in using that tool
to help us not only not only create the
query
also you can analyze it in the bucket so
let me show you what that would look
like
so I'm going to do is going to start by
going back into our properties
I'm going to grab the entire selection
here control see the kind of my cotton
case offer and just want to then go to
start to the to the sequel server itself
and that's going to launch the
management sequel the engines to you
want to take a quick look at what that
looks like
login using the same username and
password we were given and sheer in the
management studio
ok we can take a quick look at the
various databases there and remember we
are kind of continue the last night on
TV and up
click on the plus sign here click on the
plus sign next to change golds and I can
see a very robust list of all of the
tables that we have available to
connected
ok so these are all the various tables
that are located inside of that
particular data things you can see how
we can see that the list of the tables
and see that the name is very very evil
here
ok not only that is we can do a right
click here on the database name is ready
we're going to do a new query and we can
taste in the query then we've been
working on you can see if we execute I'm
will get the same error message
ok seeing that the t GL account number
is not a valid object cattle cattle two
tables you can see that that's the table
name those TG out
GL account
alrighty Gino TG all kinds of geo found
three execute them execute
TG now on the count
most comments
ok
and the TV all in jail key exists in
both tables so we have done clears with
homey in here solution we could be
specific here and say it's each or da
all right but the reality is what we
want to do is instead of returning the
key
you really want to return the account
number from this table so columns are
available on
we need these ones you can always click
on the plus sign see all the available
columns who really is the GL account
number that you want to return here
instead of the account key and actually
one of the things I love about the
management studio is is I can just click
and drag the side over here
he was a need and creating the Select
statement
ok now let's constancy out returns the
soul account number
this clear the . now also have a soul
slowly working correctly working
select soon now I can just copy that
and I take take that back into life
online microsoft excel and
look at the queries you can just simply
replace that with the one that i created
over name is equal studio single
management in you
let's execute that in microsoft excel
okay so there we have this fiscal year
fiscal period to come over and change
and if you remember from when we did get
back here we're looking at the other
data we can easily slice it into the
other natural complication and the
department and so just add a natural
education department and a creepy
treating them to assumptions in itself
into just like some support a little bit
so we're just going to add something to
this work
create the other the natural count
slicer to pick up the first four
characters location be the next three
I can't very similar oil just took off
the side
shipping is character five because the
next three characters for the location
and then I'm very slowly take off the
department has lost three characters
ok and as you saw before and now that we
have this information we can very easily
created to the table by inserting a
pivot table here so since in concept
no need to go through that again because
you saw what we did before but it's the
same idea now that I have this data then
from the whole situation from the
general ledger data that my own my own
right down to learn what I might need
you never do the yoga needs some
training or want to create to the table
very easy to grab the data and extended
break down however I need to and then
you can imagine there are a lot of
different possibilities here
I'm systems you want to do something
like an account type
ok here's one here's another idea that
you might have to do so we have to know
that it was pretty sort of data that
balance the counselor arm of lost
balance sheet type accounts
those are the things that are left hand
399 national town of 39 and nine or less
you want to do something simple like
this statement here
I don't yes the natural count
it was very equal to 3 9 line in my
listen they call this the balance sheet
taken into account
ok and if it's not then you might we do
another distinct and say that is the
natural count is less than equal to 49
then we would call this be in revenue
and it's none of the meter runs and we
would call it be expense
ok so again just to them if you have to
get some of this different ways again we
can matter on the slicers to this
information and very easily filter on
the various there is types of breakdowns
that might need and it's not be for now
we have this kind of a breakdown could
even take this and create a custom table
all right there watching want to do is
to go and take a quick look
I have two doing breakdown as as a
database query type of thing rather than
bring you the dating and pivoting in
some people like morning going just as
you create a query go and create the
query as a pivot query
so let me show you what that might might
look at look like
so the first one is going to go back to
my online connection information in
particular the specific query that it
created thus far
so we're going to use this as a starting
point so we're requiring our general
education and rather than rather than
bringing the data in here are you
getting in the same format and then
using a
by using a pivot table i'm going to show
you how we could take that same query
and creating what's called a privilege
query from from the Sun the same source
see them
let me show you what that might look
like so looking at connections that I
begin with 14 are our general ledger to
you and go to the properties here in is
simply not grab this particular query
ok and then again just going to either
use them to go back to our our query
analyzer here
so inside of single servant garden inn
to go back here to the top and we're
going to do a new query here
ok so the new queries are you just
simply as a starting point
this query that we got some this work
included from excel
alright so maybe you already know this
but for those who don't
I can actually do a very select from
another query
that's what i'm doing is i'm selecting
what I'm talking tom is modern not a
table of a basic cable
i'm selecting some query that we created
previously
I'm just like I can i can select from a
particular database and table i can do
is select from another query and that's
what we're gonna start with so select
star phone that particular query
again and we're going to need in alias
alias and so that that's what students
have a new lease of them we can select
from that particular query
so this is not this is our initial
starting point and here we can say that
we want to select the account number
the school year fiscal period cetera
ok however what we want to do to extend
this is we want to actually create want
to create a pivot of this information
right there we want to what we are going
to want to select the account number
we're going to want to select the school
year
ok but rather than selecting the cisco .
and that change what we want to do is
actually select on a change when we
pivot at begins to be this cold periods
so let me show you what I would look
like
ok so that's like the account number
fiscal year then we're going to pivot
pivot the son of the net change and ran
for the school . in
this range of possible possibilities
ok
and that would be very expensive new
processes
ok one of the possibility that i was
going to be in one or two or three
etcetera etcetera . we have to have I
have this one gonna notepad here so let
me just grab just for the sake of what
we're doing here was to speed it up
we're going to scrap all of those 12
possibilities and if you GL in your
gmail you do 13 periods and a question -
12 13
ok so that's what we're giving obvious
cisco . in this set of possibilities and
we're going to return the sum of net
change
ok so let's put it that you call that -
given
yeah
that's good
ok so let me start with select
everything from that you can see how
that returns account fiscal year
1 2 3 4 5 etc
ok so that's maybe way to to selected we
can you see how it is not fitting the
information based on the . numbers
ok now what a lot of felts want to do is
to select then society
select the account and fiscal year and
then rather than naming one two three
ok they may want to also select . one as
january and . to as a secondary or
however long the seven eight things
ok and then of course you hear you speak
to school year begins at I mean it's
january is actually sorry yes if
September
as in example is your first fiscal
period ok then he would say one as has
September to as october three years to
send Sarah in case you know I don't need
to adjust as clearly based on your
fiscal year but let me go ahead and show
you what they look like I'm just gonna
borrow although some tell me i will see
its created this one will just paste it
in so you can see what you look like
ok select account just we r 1 is january
to as jewelry etc from dyslexia and
giving it on the school . and returning
these one of the net change
ok excuse that you can see how we have
account to school year
Jane recovery marks etc and word we're
adding up all of the summarizing all of
the net change in ops in the cross
journal as you do them right and then
this whole thing can easily be taken
over into Excel since we create a new
query here
data nice horse
and you can choose our particular data
and we're going to select a particular
to a particular table we can just some
good environment to the query and
modified so in this case we'll go ahead
and slip
let's table this point to the metal we
talk select those and modified as soon
as we get it
return you to our tumeric saw we're
going back to the data and then just
change our connection properties here
sequel and we'll paste in select
students had does to pivot swords
nice
see that we are need are retiring the
data from that's on the technology data
and pivoting that data
ok alright so those were the things that
I wanted to cover in today's training
session all this is available as excited
to be done in microsoft excel 10,000
dream 2007 and 2010 all these work of
course the specifics of how you do you
do that will change and ever so slightly
in fact that let's go ahead and I have
the border so it's going to take a look
at for those are those of you that are
using still using Microsoft Excel 2003
let's go to go ahead and just jumped you
a little bit of do the same steps here
so you can see what that would look like
those of you that are in two thousand
seven or ten
I don't need to watch the rest of this
week of watching but couldn't see what
that would look like in 2003 and a
little bit different to set out and
seeing functionalities in their swords
let's take a look at that so let me go
ahead and open up Microsoft Excel
this is version 2003 and we're going to
hear that would be to do a data and
you're going to do an import external
data to create a new database query
ok I source of going to be a new data
since it was ok here and this will be
our our query again started suitable
certainly can get a lot of anyone to
know
what driver do we want to use two
tackles we're going to select the sequel
server driver and connect to it
single-serving what kind of a connection
we are doing whether it's using my
network login or are specific on sequel
user and password click on the options
here two guys and select here the date
of things don't we are watching two
queried and for our test here it's gonna
use the fact of planner public published
data and click are doing default stable
here can now select ran all the areas
tables in use available to us then in
that particular database
ok and whether it actually allows us to
select band the various columns and want
to look at the scene 1 using shoot for
evaluation for the games and whatnot we
want to filter it and or sort by using
so we can supply the not just the sort
by the account number and your kind of
day two directors times yarder went
through Microsoft query to further
filter it and analyze it before we
trying to do those wouldn't that case
let's go just right candidate adhere to
our to our plan she can you can see here
we have this little old King the father
into medical eerie look at the data
range properties or refresh the data
ok you refresh the day that's going to
run the query again
now we talked about previously
my first level of analysis to use the
filtering and as well as creating them
additional tools and salami
let's start with the concept here doing
the additional breakdown so we're just
to the natural counter
ok previously good would apply then
we're going so we're going to take our
first
first more character than from the
account number has a natural to do we
need the account number one through four
in the prior version want to go ahead
and apply that to the entire return to
date of set
I mean this version it's not simply
going to go ahead and do it in a small
town does that information
applying that same culture than I know
that some sort of that starts me
function cell function into the entire
range and do the same thing for location
and apartment i'll also wanted to show
you then the concept of filtering in
this version it was automatically
attitude
she's not in 7-10 you want to go and
just do an audio filter and that will
give us have seen filtering capabilities
we want to them to survive a particular
account longer more buying all those by
a particular column group so he
we can do the same functionality we just
have to work within the parameters of
that particular version
all right not only done that was going
to deliver some more thing
actually since when do the department
here
she's a fighter breakdown
all right that's the same concept here
we're doing the mid of you , moment
follows especially eating this quantity
through the accessory characters and i
just applied that to to the whole range
of returns diva
ok
and when we refresh the data bring them
you said it does apply D natural and the
department to that you might be trying
to do them so that certainly helps out
now for the pivoting how we can come
here and go to the data and right under
the data menu choice we have a create
pivot table handle table and chart
what is going on existing data inside of
excel and we want to be time to the
table 2 more actually . so you want to
create a pivot table and return it to
you are using this other sources 2011
returning to be working and that will
create a new sheet and they can now draw
bar to the information herein the under
the little form that shows here so
page field will be our our column group
are you school you and our department
then our rose is going to be our account
number before our college or entity our
periods and what we're displaying here
would be the months and if we want to go
ahead and start filtering your little we
can choose our budget a particular
fiscal year end and or one more
departments
we're going to get
the other scene seemed to have the
ability to hear you exhale 2002 just
some different ways to to get here and
accomplish the same things
all right either think they want to
cover to date and in this training
session
hopefully learn how to do it and can
apply this not only like the plan our
customers but anyone is using Excel has
a need to the query to create whatever
database source Mikey and then analyzed
the data using the filtering as well as
a pivot tables and as an added bonus
show you how we can actually create a a
to the query to bring the dating and
already fitted sleeve
ok I wanted to join you and seeing you
again and at a future training session
thank you