Hello and good day everyone.
How were you day? It’s good or not?
Okay, 😊 today we need to learn new things.
Did you not that Microsoft Excel is probably the most efficient and popular Data Analytics tool available which is utilized by organizations all over the world. However, many believe that acquiring skills in MS Excel would not help them secure a promising and high paying career, which is certainly not the case.
Today we will learn about some trick for Microsoft Excel that can be used to Sales and Marketing Professional.
Sales and marketing are combinations that are always associated with each other. Sales are teams whose job is to "sell what is in stock". Sales also develop relationships with customers. Meanwhile, Marketing changes the target understanding of tools and tactics to attract market interest, build relationships.
Whether you're a digital marketer, responsible for sharing, or spending your ads will need to base your decision on the data ahead of you. To do that, you need Excel. With a broad understanding of the two key areas of Excel, you can look at patterns, improve the process and expertise in Sales and Marketing.
According to Skillsology website, there are 2 main areas:
1. Data Cleaning and Preparation
2. Data Analysis
Today we will learn about Data Cleaning and Preparation.
Data Cleaning
Is the data dirty? Why needs cleaning? Data cleaning in Excel is about getting your data in a format that you can effectively analyze from. It is your work base.
Technique 1 - Sort Your Data Regularly
Whatever you do in excel, the first thing you need to think about is how you want your data organized and that's exactly based on the main objectives. This is to ensure you can analyze it a bit quick to check the numbers you need so far.
Two techniques of data preparation regularly;
- Identify inputs clearly, calculate and output. Explain by describing the cells that perform calculations. For example data input cells and outputs/outcomes. Excel has some fixed formats that are useful to help. It is available at Home> Cell Styles. Use it to clarify which cells are inputs, which perform calculations and outputs.
- Do not embed input in the formula. You can see below that C1 and C2 will achieve the same result, the difference is that it is very clear in C2 what all the inputs - nothing is hidden!
TEXT TO COLUMNS & REMOVE DUPLICATES
Remove Duplicates do what is said in the select data range, press Remove Duplicates (Data> Data Tools section) and any duplicate values within the range will be diverted. It has an impact on many different situations.
Text to Columns is a bit difficult but very useful. It allows you to separate data strings into multiple cells.
Let say, you have a list of names that you want to split up with first name and last name. Start by highlighting the column/row you want to change, go to Data tab and in Data Tools choose Text to Columns. You now have the option of customizing data over a limited or fixed width. Delimited basically means what is common in the data, this can be a comma, column or space. Fixed width stands for a fixed number of characters. The latter is great if we separate something long standard but for this purpose, you choose the restricted option.
The second step is to select a delimiter, in this case, we will choose the space. You can see in the Data Preview what you will get.
The last step is to select your data format. In most cases, you will only leave this selected as General. I've found the Date format useful if you want to change the date formatted in US style (Month / Day / Year) to English style (Day / Month / Year) or vice versa.
Lastly, Excel that works shows the text is separated into different columns. Below are the results.
TRIM & IFERROR - TWO EXCELLENT DATA CLEANING FORMULA
First, TRIM. The TRIM function only skip text in the cell any additional space (and not necessary). It leaves space between words but removes them from the beginning and end of any text string. Hence often the function or formula may return the wrong result because there is a space after the word in the cell. The TRIM function is as outgoing and easy to use.
Syntax;
= TRIM (CELL THAT I WANT TO TRIM)
If you are working on data set with errors such as #N/A and which mess up your analysis, then IFERROR can really help.
Let's say you did VLOOKUP, your formula might look like this:
= VLOOKUP (Q2, D: F, 3, FALSE)
If that returns the feared #N/A for many results but you just want their discount from your analysis then you can move your errors into empty cells with IFERROR:
= IFERROR (VLOOKUP (Q2, D: F, 3, FALSE), "")
VLOOKUP is still valid but if an error occurs, IFERROR will enter and return empty cells. Empty cells are achieved by placing two quotation marks together "". Excel will return the value of the text contained in the quotation marks, you can put a word or phrase there like "NOT WORKING" and it will return it for the error value.
That’s all for the data cleaning and preparation. Now we move to the next which is data analysis. You might be fun right because you have clean data now. Did you know that there are a few functions in Microsoft Excel that can make your data analysis more organized.
Let’s explore below!!!
- PivotTables - the best and fast data analysis function in the Excel toolkit.
- COUNTIF, SUMIF, VLOOKUP, INDEX MATCH - an important formula for data analysis
- Conditional Formatting
PIVOT TABLE
A pivot table is a table of statistics that summarize the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.
Okay now, let's see how to do Pivot Table.
1. Make sure your data has headers. Each column of data to be placed in PivotTable needs to have title/header otherwise Excel does not allow to create PivotTable.
2. The number goes to the bottom right of the "values" box in the PivotTable field grid. Excel will automatically include the numbers if it detects all numbers or their COUNT if it detects non-numbers (eg #N/A). Use Value Field Settings to customize this.
3. Use Filter to remove elements from PivotTable or use a slicer better to do this dynamically.
4. For advanced users, the PowerPivot function connects your PivotTables to an external data source (ie SQL or Access). You can then refresh the data in your PivotTables with one click. This is very often if you have regular sales reports to generate.
COUNTIF, SUMIF, VLOOKUP
You must be wondering right what is COUNTIF, SUMIF, VLOOKUP. For your information, there is a formula that used for Sales and Marketing analysis. Below are what it is and how to use it.
COUNTIF is a number based on given criteria. If, for example, we want to see how many times a certain product appears in a large list of our multiple products will use COUNTIF. Its syntax looks like this:
=COUNTIF(RANGE, CRITERIA)
The range is the data network that we see, in this case, that will be a big list, we can say that column A, we will probably choose everything = COUNTIF (A: A,... The criteria are the item that we want to look up, let say it found in Cell B2, the final formula will be read = COUNTIF (A: A, B2). Excel will then calculate how many times the values in B2 appear in column A. Always keep in mind that this works for text or numeric value.
Okay, we move to the SUMIF. Let’s go.
SUMIF is a variation on COUNTIF, it's a very similar function but the amount is not a charge. The syntax looks like this:
=SUMIF(RANGE,CRITERIA,[SUM RANGE])
SUMIF start like COUNTIF but for the first, you should choose a range. Using the example in the image below is column B with login names. Then you select the criteria you are looking for, in this case, the cell D3 - Dave. Then you need to add the final part of the formula that is the sum of the total. This is the range you want to add together based on the criteria you are looking for. So, in this case, you can see the total number in the Total space allocated to Dave.
VLOOKUP stands for vertical search. It's a way to pick up a particular value, seeing that in a separate table and returning the adjacent value from the table. VLOOKUP is an Excel function to lookup and retrieves data from a specific column in the table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.
The syntax looks like this:
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM[RANGE_LOOKUP])
That might seem complicated but when you start using it, you'll find it easy. Let's take this formula by example. Imagine you have a customer email column with the date they become customers in the adjacent space: CUSTOMER EMAIL, DATE TO CUSTOMER. You then have your latest sales list, the column looks like this: CUSTOMER'S EMAIL, SALES SALES VALUE, MORE DATE. You want to see the date the customer first became a customer. The unique identifier here is the customer's email so that's your lookup value. The column layout is the table you want to look in, in this case, is the table with the subscriber's email list and the date they became customers (this does not have to be a table, but it is recommended).
This is important - the value you are looking for, the email, in this case, must be in the column away from the data/table you are looking for.
The next part of the formula is the number of column index, or how many columns you want to calculate. The column you are looking for is 1, the next column is 2, which is one we want in this example. The final part of the formula (distance tracking) is whether you want an exact match or an approximate match. If you place TRUE Excel will return the next largest value lower than the value of your search. You will only actually use TRUE in favor, for most searches you will use FALSE - exact match. This will return the date each person first becomes a customer into your sales data sheet, removing it into PivotTable and you can see how many people participating in different months spent spending.
CONDITIONAL FORMATTING
Do you want a way to make your data more light based on specific criteria? No more conditional formatting needed. This feature in Microsoft Excel can make you look like a spreadsheet pro and it's very easy to use, you will be wondering how can you have not done it for ages.
Conditional formatting allows you to change different color cells, add traffic lights to them, or data bars and rows based on information or numerical values in the cell. You can set many different rules and actually create the data you want to see to keep pages away. Among the most popular rules are:
• Greater / Less Than - Highlighting cells based on them becomes higher or lower numerical values
• Text that Contains - Highlights cells containing specific text
• Equal to - Highlight the cells that are the same as the value you choose
• Top / Bottom rules - Select multiple rules based on numerical values. This may be the top 10%, or items above the average range.
• Duplicate Values - Simply highlight duplicate values.
You can also use Data Bars and Color Scales to highlight the largest and smallest numerical values in the data range.
Hopefully, this entry will useful and helps your to highlight some areas where you need to focus your Excel practice if you are in Sales and Marketing.
The best way to master all these functions and the formula is entering the excel and practicing it! If you think it the time to start your journey mastering and get an important skill, please take a look at our Excel training course if you don’t understand by practice alone. That’s all, for now, see more for more content that will help you more. Don' forget to follow this blog. If anything that you want to ask to feel free in the comment box below...Thanks 😊😊😊