Hello and welcome to A1 Excel! This post will go over basics in dataset specifications that can be accomplished through simple formulas. Sometimes datasets aren't the neatest in terms of formatting for pivot table purposes or you may wish to know something more specific that isn't a parameter in your dataset. I've utilized the formula Concatenate and formatting for dates to gather cleaned up data that I can easily use for pivot tables, summaries, lookup formulas, and more.
Let's get started with Concatenate! This formula combines strings of cells together to form a specific data chain. In the example below I'm showing pet vet clinic visit data. Let's say we wanted to get a specific parameter from this dataset like "Pet Type-Vet Name" so we would use Concatenate to get that result.
Formula:
Result:
Now that we have this new data parameter we can rename the title in the column and create a Pivot Table to see how much in vet bills did each vet earn by pet type:
As you can see below Dr. Max when working with Rabbits has earned the most over the years at this vet clinic:
Building off of our vet clinic dataset let's do one more example utilizing text functions. This example in particular is something you will want to keep on hand for dealing with billing, invoicing, hire dates, or other types of data where dates are concerned. Sometimes in reporting you may be unable to have a simple Month/Year columns, instead you will likely get dates like 5/15/2015, 4/13/2017, 4/5/2021, etc. This is normal as that would be the actual transaction date, however, for report/summary building purposes this isn't that helpful. Realistically you can use formulas to pull out just the month/year from the dates. I've learned over time that you can use one formula to pull out a reformatted date into something more summary/report friendly.
The important detail to note is that the Text formula is very customizable. In the example below I personally like having the shortened month and fully year combination. This can be accomplished with a formula like ="A2,"mmm yyyy" (you can copy this formula into your own Excel file to practice if you like).
Example:
Result:
As a side note, I recommend highlighting the cells you've modified or added columns on for with formulas in order to keep it clear what's from the report and what you have modified.
These tools are useful for basic dataset modification with changes that are trackable. These formulas aren't complicated but they are easy to remember and very effective in action.
Comments