Excel Salessales in Excel
No matter whether you are a marketing company, in charge of partnership or advertising spending, you need to make your choices based on the information you have. You need Excel to do this. However, I strongly believe that one does not necessarily have to know everything in order to be able to derive significant conclusions from the information.
Having a wide grasp of two core areas in Excel, you can identify paradigms, enhance process, and become the sales and marketing superstar for whom you were made. I will guide you through the features and formulas I use most often in cleansing and analyzing my work. It is by no means a complete listing, but will hopefully help you guide your own study and your own personal growth.
Getting the files messy? "Get the mopp Alan, this information is messy! Excel file cleansing is about getting your files into a file that you can analyze efficiently. It' s unbelievably important because the information you are likely to use is inevitable. CVS files downloaded from third-party applications you use, be it a CRM (Customer Relationsmanagement) or payment system, a tracking system you use, or your own website.
This can be information from a customer that is supposed to give you insights. You can have this information in all forms and dimensions and you have to convert it into a standardized (nice and clean!) form to begin your work. In Excel, whatever you do, the first thing you need to think about is how you want to organize your information, and that is really what your final goal is.
Clearly identify which cell is doing calculation, which cell is doing calculation, which cell is doing entry, and which cell is doing output/results. In fact, Excel even has some convenient default styles to help you do this, which are available under Home > Cell Styles. You can use them to make it clear which cell entries are, which computations are performed, and which results are made.
These two I use all the while when I clean up dates, they are two of my favorites, especially text to columns. Remember Duplicates does what is written on the can, selects a datarange, clicks Remember Duplicates (Data > Datatools section) and all double entries within that section are deleted.
Allows you to split chains of records into more than one cell. Let's say you have a shortlist of surnames that you want to divide by first and last name. First, mark the column/row(s) you want to modify, go to the Dates page and choose Text to columns in the datatools.
Now you have the possibility to adapt the datas over a limited or fix width. Limited means essentially anything that is usual within the dates, this can be a point, double or blank. Stage two is to pick your separator, in which case we will pick a blank. In the preview you can see what you will get.
Finally, choose your file type. I' ve found that the date form is useful when you want to convert U.S.-style date formats (month/day/year) to British type (day/month/year) or both. Smash your text, Excel does its work and your text is divided into different rows.
So many formulas are available to help you clear and organise your information. Assuming you are doing a SLOOKUP, your equation might look like this: You can enter a text or phrase such as "NOT WORKING", and this is returned for the errors returned.
Well, for laughs - the analyzing of your datas. Here are some utensils I use regularly: Some Excel pureists say that their applications are finite, but I still need to find a faster and more versatile way to analyze them. PivotTable is a versatile method of dragging and dropping to count, average, sum (and so much more) records.
Essentially, if you have rows and rows of products or sales dates, a PivotTable can quickly aggregate them by a shared multiplier, be it a date, line, or other item in your dates. Ensure that your information has headings. Every dataroom you want to add to your PivotTable needs a title/header, otherwise you cannot generate a PivotTable in Excel.
If Excel recognizes all numbers, it adds them up automaticaly, or if Excel does not recognize any numbers, it COUNTS them (e.g. #N/A's). The PowerPivot feature allows experienced PowerPivoters to connect their PivotTables to an outside datasource (e.g. SQL or Access). Then you can update the information in your PivotTables with one click.
It is especially useful if you need to create sales reporting on a periodic basis. Except for =SUM, these are the formulas I use most often for sales and marketinalysis. For example, if we wanted to see how often a particular item appears in a large listing of several items, we would use a Countiff.
Area is the area of the information we are looking at, in this case this would be the big one, let's say this is colum A, we would probably just choose everything =COUNTIF(A:A,.......... Then Excel would calculate how often the value will appear in columns A in B 2. It is noteworthy that this works for text or numerical value.
Based on a very similar approach to COUNTIF, we first choose the area. By using the example in the figure below, this is B with the name in. Then we choose the search criterion, in this case D3 - Dave cells. Let us then append the last part of the equation which is the summation area.
That is the area we want to summarise according to the searches we have. In this case, we can see the summation of the numbers in the Totals columns assigned to Dave. Beloved and equally disliked by many Excel people. Just think, you have a list of e-mails with the date on which you became a client in the adjoining column:
Then you have a listing of your last sales, the column looks like this: CUSTOMERS E-MAIL, OVERALL SALES VALUE, DATE OF PURCHASE. This is the chart you want to look up, in this case the chart with the client email lists and the date they became clients (this doesn't have to be a chart, but I suggest it).
It is important - the value you are looking for, the e-mail in this case, must be in the leftside pillar of the data/table you are looking for. Next part of the equation is the number of the index or how many colums you want to number.
You are looking for a 1 columns, the next 2 columns we want in this example. At the end of the formulas (Range Lookup) is whether you want an accurate or rough fit. When you set TRUE, Excel returns the next larger value that is lower than your search value.
Returns the date when each individual first became a client in your sales datasheet, throws it into a PivotTable and you can see how many individuals who have acceded in different monthly periods are spent - boost. Would you like to have a possibility to have your personal information displayed according to certain criterias? conditional formating allows you to transform your documents into different colors, adding colored indicators or rows and rows within a document according to the information or numeric value within that document.
There are many different ways you can create a rule and really remove the information you want to see from the page. One of the most beloved rule is: Upper and lower rule - Choose different rule types according to the numeric value. These can be the top 10% or objects that are above the mean reach.
They can also use datebars and color scales to emphasize the highest and lowest numeric value within a datarange. I hope this has been useful and has help to identify some areas where you should concentrate your Excel practice when you are in sales and distribution and when you are not in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and marketing when you are in sales and when you are in sales and marketing when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are in sales and when you are when you are in sales and when you are marketing and when you are when you are in marketing and marketing.
Of course, the best way to learn all these features and formulas is to come to Excel and practice! When you think it's your turn to begin your Excel trip, take a look at our Excel course - usually 49/$69, but you can register for 7-day free entry here.
The online Microsoft Excel course is one of the best-selling securities in the world.