VLOOKUP 101 PART 5

Merging tables using Vlookup

Combine information from tables

Combining tables was is a very day-to-day task that mostly Excel Pro User has been doing, for ages! This is simply because they are trying to retrieve information from another table and join it into a complete table. Sometimes they do that is also because they’re trying to cross checking data from another table.

This usually happened in between a transaction table or some maybe calling them the facts table and database table (or db table) “Well there are so many ways of how people calling these tables…I can go oon for 3 days if I were to list down all of em”. So, what’s the difference in between these facts table and db table?

Continue reading “VLOOKUP 101 PART 5”

CALENDAR TABLE WITH DAX Part.5

Hierarchy in PowerPivot with Calendar table

In order to use create hierarchy field with your Calendar Table, you will first have to extract the necessary date component. If you have no idea what’ I’m referring to, I’ve talk about in the previous part of Calendar Table with DAX articles.

If you are dealing with many dimension table (AKA database table) with many fields in it, in a way hierarchy can help makes it easier for you to manage those fields into one. I guess you know how crazy it can be scrolling up and down searching for that field while building report using Pivot Table of creating visualization while using Power View.

Continue reading “CALENDAR TABLE WITH DAX Part.5”

CALENDAR TABLE WITH DAX Part.4

In the previous article I’ve talked about how to extract date component like Month and quarter. We’re going to continue the other date component here

In this part, we’re going to talk about how to extract Week Number, Financial Year. Plus I also added the importance of “Mark as Table” and the trouble you’re going to run into while sorting the text-based date component.

Continue reading “CALENDAR TABLE WITH DAX Part.4”

Microsoft Excel: Deadly sins of maintaining list in Excel sheet Part 3

This will be the Part 3 of the Deadly Sins of Maintaining List in Excel which we have covered the first 2 part earlier.

Part 1 : Microsoft Excel : Deadly sins you should not do in Excel (Part 1)

Part 2: Microsoft Excel : Deadly sins of maintaining list in Excel sheet (Part 2)

Each data belongs to its own column

While you’re designing a table or list in Excel, plan ahead is extremely important. Always remember, while you’re designing a table, plan and think of what you need, what kind of the report that you will need to create at the end month or the end of the year. From there, you will roughly know what sort of data you’re require to collect during data entry.

First, you must plan how many columns you need, name each column as what you need to collect in its column. Each column only limited to one types of data type.

If you have seen this kind of tabulated data and calling it a table. Well, you’re not quite right. This is a report, static report not a table or list.

Continue reading “Microsoft Excel: Deadly sins of maintaining list in Excel sheet Part 3”

Microsoft Excel : Deadly sins you should not do in Excel (Part 1)

In this series of articles, I will be talking about things you shouldn’t be doing in Excel. No, you will not become power user after reading them. But, you will definitely have more understanding and be able to manuever Excel easier. Follow me around and I will make you realize there are a lot of things you’ve been doing may not necessary is good, and of course I will follow with some solution for a quick fix.

I once read this article from an author Kay Boogaard, she too wrote about thing you should know about Excel. In the article she mentioned that she met her husband who he converted her from a person who would rather lock themselves in the phone booth full of mosquitos than having her eye crossed starring at the rows and columns in Excel into an Excel lover. People start hating Excel reason is because they do not understand why Excel behave in such and things always turn out the other way around. I can totally relate that because majority of us are self-taught Excel user. And when you are a self-taught user, you may not necessary learn or use it by googling “Excel 101” or “Do’s and Don’ts with Excel”. You dive right into the worksheet, and when things don’t seem right, you google it only when you’re in trouble. Continue reading “Microsoft Excel : Deadly sins you should not do in Excel (Part 1)”