How-To Create An Excel Query!

This short 3-minute video gets right to the point and shows you how to use your own Excel's name-ranges as tables in an Excel Query!!

---

Closed Caption:

yeah
so either Benjamin sharp och and here
doing a query today I'm glad you're
joining i'll go ahead and show you how
to do a Microsoft query but to be clear
the type of query are going to use today
only requires excel you need access or
anything related to true database and
then do it all excel and show you some
tricks that I've learned to do a query
let's get started on my left
I've got a table this table has 14
numbers and names and on the right I've
got a payroll table got four columns
date amount just remember paycheck but
the most important column being social
security number
why the links back to the first table
let's see what that does
I'm very important part of this whole
query is a name your Excel ranges so the
one on the left
i'm going to highlight it and a college
employees table simply highlight it put
your cursor in the name box in Excel
type in employees table and enter you
can make it any name you want doesn't
matter it's up to you
this will be the name that excel we use
for your table as an alias i'm going to
go ahead and name the right-hand side
now let's move on to making the query
the next step is choose data tab choose
from other sources pick from Microsoft
query select Excel files this is an
excel file choose the excel file that we
say which is this excel file and hit OK
this now brings to choose columns let's
choose the columns we want the outputs
my case let's use both the employee
table and choose three from the paycheck
table you see the results on the right
it next
I'm gonna join the SN field to the table
we have a common link my output it back
to excel and put into the table i'm
gonna drop it somewhere on Excel the
output you can choose where you want to
put it in ok we now got our excel pivot
table to use a pivot table select fields
rearrangement name a check on row date
in the column and now got a usable pivot
table
here's a really cool part we can update
a record and mr tables refresh the query
and it will automatically bring results
over
it's a new record up here insert shift
cells down and a paycheck number 13 new
date and now we're going to refresh the
query you see now the new record has
come into place now you see the power of
Microsoft queries