Questions

Hello readers! I hope everyone is having and amazing week so far. :)

I am asked from time to time,

• What should I learn to do what you do?
• How did you learn to do what you do?

These are really good questions!

Of course, all my answers tend to be... it depends. HA!

It depends on what you are trying to achieve and what your comfort level is with the material. But, I am sure you don't want a wishy-washy answer like that. So, I will give you three things that I think will help anyone wanting to play with data. And they all start with Excel.

Tables

Let's talk tables. What are they and why do I think they are helpful. I think tables are a secret gem that once discovered you will ask yourself, why haven't I been using these all along?!?

To me, tables kind of make your data "smart". For example, when you create a table:

• Excel forces the column headers to be unique.
• formulas now utilized structured references as opposed to cell references.
• you don't have to worry about selecting all your data when you sort.

That last bullet has saved me more times than I can recall! How many times have you sorted your (non-table) data and noticed your stuff did not sort correctly because there was a random column with no data? Yeah, me too.

Another reason to use tables... for those who do not use them, you look like a wizard. ;)

Here is what Microsoft says about tables: link here.

Here is a video on how to create a table: link here.

Pivot Tables

So, you now have all your data in tables and life is good! Your data is nice and organized, formulas are "smart", things just work. But now you want to slice-and-dice and start getting some aggregated counts on your data. You could filter and count but that is slow and potentially error prone. So, what can you do?

Time to add a pivot table!

Pivot tables are a great way to "summarize, analyze, explore, and present summary data."

Let's say you have Room data. In your dataset you have square footage, Space Classification, department allocation, parent floor, and parent building info. With a pivot table you could drag the info you would like to aggregate into four areas: Filters, Columns, Rows, and Values.

Below, I have Building Name and Floor Number in the Rows area and two fields I want to aggregate, Room count and Room SQFT (a sum). If I wanted to make a visual from this, I would add a pivot chart.

Much easier than filtering the raw data!

Oh, and if you want to really look like a wizard, double click on any of the Values cells. A new sheet will be added with only the data pertain to that value.

Here is what Microsoft says about pivot tables: link here.

Here is a video on how to create a pivot table: link here.

vlookup

I know there is a new lookup function called xlookup. But I think vlookup takes care of 90% of what needs to be done when starting out.

This is one of those functions that once you learn how to use it, you will never look back! That and it is another one of those wizard moves that non-vlookup savvy people are always amazed at!

When I first started and learned how to use vlookup, I used it

All. The. Time.

I am not going to go into how to use it, as there are tons of instructional videos out there (like the one below) but I will say, if you have two (or more) sheets of data in your workbook and you want to bring something from one sheet to the other, vlookup is your tool.

For example, let's say I have a tab with Building info and one with Floor info and I want to added a Buildings Region to the Floor records. As long as the Floor table has a key to connect it to a Building, say a Building ID/Code, you will be able to bring over Region.

Now, if you wanted to create a pivot table from the Floor data, you could filter on Region.

Not to shabby!

I'll tell you, once you get to know vlookup, it will be your new best friend!

Here is a video on how to use vlookup: link here.

Now to answer the second question, how did I learn to do what I do?

It is not a pretty answer! Google, trial-and-error, and Lynda.com (now LinkedIn Learning). Later in life, I got a B.S. in Data Analytics. I did learn some good stuff from that but I will tell you, most of what I use today was all self taught. I'm not trying to be cool, just saying, you don't necessarily need a degree and most of what I do/did was all on-the-job training that wasn't taught in any formal setting.

I am not a salesman but I would highly recommend LinkedIn Learning. There are a lot of really good training videos. Of course there are a lot of amazing training videos all over the web but I am speaking of what I used and when I started, it was Lynda.com.

You may want to check with your company or your local library. I know both of mine offer free access to LinkedIn Learning. If yours does not, check with a nearby library that does. Most times, an out of county library card fee is cheaper than the membership. (but you didn't hear that from me!)

I will also say, I ask a TON of questions to anyone willing to listen and help. So, here is my invitation to you, feel free to ask me! If I can't help, I will try to point you in the right direction!

Well that's it for this week! Thanks for reading and if you have any questions, do not hesitate in adding a comment or messaging me. I am always willing to help!