(upbeat music)
- It's often said that business analysts spend up to 80%,
of their time, not necessarily doing analysis on data,
but rather gathering and cleaning up their data,
in order to then, come to conclusions about it.
And Excel can help make this process faster and simpler.
Now there's a lot of different, neat tricks, that Excel has
to integrate with some software programs,
but one of the good starting points is,
if you're using other systems,
Excel can act as a connector between these,
because it's a very popular format
to be able to export data in.
Let me show you an example.
Now I've got some data here and maybe I want to
add to this data using other data sets.
But the problem is that my data is in another program.
In particular, let's pretend it's hypothetically,
in a program that's called Stata. Okay.
So I've got a data set here that's in another software
program called Stata.
And the reason that we might use Stata for some of our data,
like many database programs, is that it's really good
at handling large amounts of data.
So this particular data set isn't that big,
it's only about 51,000 rows of data,
but as we look at the number of variables,
there's hundreds of them, right?
It's a very, very large data set, just in terms of the
aggregate number of variables that we're looking at.
Well, if we wanted to export this data into Excel,
to be able to integrate it, we can do that really easily.
Stata offers a convenient export option. And if you notice,
we can export not only to Excel files, themselves,
but also as a CSV file.
And of course you can open CSV files using Excel.
So, Excels a great tool to kind of connect all of your
different software programs that are out there.
But, even after you've pulled your data together,
Excel can also help you when it comes to kind of presenting
that data and integrating with other tools.
Let me show you an example of that.
Okay. So we're back in our data set now,
and we've got data on different finance variables, right?
So this is a capital expenditures as an example,
the total assets and a variety of other kind of finance
variables, and therefore different companies for different
years over time.
So we went through and simply used an average IF function
to go through and kind of summarize some of this data.
But maybe we want to go through and present this data in a
graphical form. Well doing that, of course,
we all know that we can insert a nice Excel chart,
kind of highlighting this data.
The nice thing about Excel though is,
not only can we keep this chart in Excel,
but we could also put this chart into another tool,
like say PowerPoint, for instance, and because it's linked
to Excel, when this data changes, if this data for instance,
instead of a 0.07, if we were to make this a point,
let's make it pretty obvious so that it shows up, 0.5.
Now of course the chart updates dynamically, unsurprising,
but what's interesting and useful is, and I'll change this
to example, what's interesting and useful as we can take
this chart right out of Excel, so I'm going to copy this,
and then we can put it into PowerPoint.
And as long as we paste it properly in PowerPoint,
it'll auto update, with any changes, in Excel.
This is really useful because, when we go to change our
Excel data, it'll update on that PowerPoint spreadsheet,
and we don't have to recreate that same PowerPoint deck
every single time. Let me show you how this works.
So we've copied the graph.
Now we're going to go over to PowerPoint.
Okay! Now I'm here in a blank PowerPoint workbook.
What I want to do, I'm just going to remove this text field,
I can shrink it down, but I want to do a paste special.
Okay. In particular,
I want to come through and paste by right clicking and then
select this paste option,
keep source formatting and link data. When I do that,
Excel and PowerPoint are now linked together.
And so any of the data that I change in Excel, that change
will flow right through to PowerPoint, even after I saved
the file.
I'll have to update my PowerPoint when I reopen it,
but it's a great way to reduce the work involved with
creating marketing presentations or pitch decks
of any sort, really.
Let's take a look and see how this updates, we change
the data in Excel.
So, we'll be right back here.
We're going to go over to the Excel file again.
All right. We're back here in Excel.
Now I'm going to go ahead and change this back to like 0.07
and, just for fun to, so that we can illustrate this,
I'll make this variable, let's say 0.1.
So we see that the orange bar here on the far right in the
graph shrinks down.
Now let's go take a look at it in PowerPoint.
Voila, right? Like magic.
This makes it so much easier to create dynamic pitch decks
so that if you have a presentation you're doing on a regular
basis, say once a month, for a marketing presentation, or
once a quarter for investors or something similar,
this makes it really easy to go through,
create those standard set of graphs that you're going to use
each time and you don't have to rebuild it every time you're
going to to do the presentation.
Excel works great for pulling in data, works great for
spitting out data.
You can think of Excel as a link between all the other
software tools in the toolbox.
Now you should be ready to start putting this into practice,
for data, in your own business.
(upbeat music)