Author name: Ben Richardson

We are living in the age of information and business intelligence is becoming the norm to make better decisions. With billions of people using social media, the amount of data is huge. This data can be a gold mine for businesses if used efficiently. [playht_player width=”100%” height=”175″ voice=”Mark”] By using data, you can evaluate your social media strategies and gauge the effectiveness of your marketing campaigns. You can find a lot of analytics tools offering almost the same services with a slight degree of variations. Learning to use Excel effectively is one of the most efficient ways to derive insights from your data. With Excel, you can build an interactive template to analyze your social media strategies and see what’s working and what’s needs to be changed In this article, we will try to build a basic social media Excel template using different features of Excel. Buckle up! See the above worksheet where basic information for the last 10 days of Twitter postings is recorded. We will be using Excel features to make it a proper template that can be used for future analyses. Here is a quick tour of the above worksheet Column Name Description Date of Post Date when the content was posted on the platform Post Content The subject of the content Category Categories of posts defined by the firm e.g., educational posts, announcements, etc. Retweets No of the times a post was retweeted by users Likes Total no of likes on the post Mentions Total no of mentions of the handle by users Clicks Total no of clicks by the users Engagement Sum of all the retweets, likes, mentions, and clicks   Now let’s roll our sleeves to make it a professional-looking template that is easy to use and gives you actionable insights. Date Formatting Date formatting is used to adjust the dates according to our preferences. Excel has a long list of predefined date formats that are being used in different regions. Like commonly used date formats in US and UK. Apart from the predefined date formats, you can also create a customised date formatting style. In our template, we will be changing our dates from UK standards to a standard US format. To do that, first, select the entire date range and then right-click to open the Format Cells menu. The shortcut key to open the Format Cells menu is CTRL+1. After clicking the Format Cells…., a new window will appear. From there, select the Date from the Category appearing on the left side of the window. After clicking you will see a long list of predefined formatting styles on the right side of the window. These are relevant to the location that you can see below that list. If you want to change the location, click on the drop-down menu available below the formatting styles and select the location you would like. In our example below we are currently showing UK options but selecting to change to US options. After clicking the English (United States) option, all the commonly used formats in the US will be available for you to choose from. After selecting your desired formatting style, click on OK and your dates will be formatted accordingly. Data Validation Data validation is an important feature in Excel that is useful to avoid data input errors. It makes sure that data is in the desired format when entered into the sheet. We can use data validation in the above sheet to ensure that only whole numbers are fed into columns D to G. This would eliminate any handling errors during the data recording process. To use data validation, select the required range and then click on the Data Validation icon in the Data tab. A new window will appear to input the requirements for data validation. In this example, we have selected Whole numbers from the drop-down. After selecting the Whole numbers option, you need to enter the minimum and maximum values also. In the above example, minimum 0 and maximum 10000 are entered. After entering all the criteria click on OK. Now the selected data range is protected from any data handling errors. Let’s check it out. When a decimal number is entered in one of the cells from the validated range, Excel will show an error that the value doesn’t meet the validation criteria. There are plenty of other options in the Data Validation menu that could help you make your worksheet error-free like defining the minimum and maximum value to be entered or a customized error message when the criteria are not met. Number Formatting Number formatting is useful to give a professional look to your worksheet and make sure that is easily readable by your audience. There is a wide range of number formatting options that Excel offers as you might expect. You can format your numbers as currencies, whole numbers, decimal numbers, decide the numbers after the decimal, and many more options. For our template, we will apply number formatting to include thousand separators so that large numbers are easily readable. We will apply number formatting from column D to H, to do that, select the range of numbers and right-click anywhere within the selected range to open the formatting menu. After clicking OK, the numbers will be formatted as below. IF Formulas IF formulas are helpful in setting up conditional columns to segment your data. You can use IF formulas with single or multiple criteria. In our template, we will use the IF formula to identify the potential of the post based on its category. We have 3 categories in our post and based on our data the potential for post categories is as follows: CAT A: 1,000 CAT B: 2,000 CAT C: 3,500 Now, we want our template to automatically identify the potential based on the post category. Let’s insert a column named “POTENTIAL” to identify the potential of the post based on its category. Below is the formula that we have used: =IF(C4=”CAT A”,1000,IF(C4=”CAT