A centred title looks so much better in Excel reports or charts. I have seen many users and even management requests for the chart title to be in the center of the report or Excel chart.
There are several ways to center a report title in Microsoft Excel. And most of these techniques will work in any version of Excel. So if you have had any of these versions of Microsoft Excel, you are safe.
This tip is applicable to Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 or the latest Microsoft Office Excel 365.
Please check out both of these methods and my warning about their usage. Finally, read my preferred and recommended actions while centring titles in Excel spreadsheets.
Method 1: Merge and Center Title Text
Step 1: Select the range of cells where you want to center the text item. Take note that the text you want to center must be in one of these cells. Further, there should be no other text in the selected range, or it will be overwritten. Microsoft Excel will keep the text in the top-left cell of the selected range, and discard any other text in the selected range.
Step 2: Click on the Merge and center button. This is present on the Home Tab, under the Alignment group. It looks like 2 cells on top, a double-headed arrow, and 2 cells at the bottom.
The selected text is merged and centered in the selection. If multiple rows were selected, the centered text will be at the bottom row.
In case you want to align the text in the middle too, you have to click on the Middle Align icon in the formatting toolbar. Now the text should be exactly in the middle horizontally and vertically.
CAUTION: While you may like this method of merging and centering text, because of its ease and convenience, we do not recommend to merge cells in Excel worksheets.
All Excel pros adhere to this rule – never merge cells, unless you are presenting it in a report or dashboard, as an end result. Raw data should never be in merged cells.
Merged cells can cause havoc while trying to sort data.
Basically, you can’t sort data if it contains even a single merged cell. So you have to unmerge the cells before you can sort it.
Further, data in an Excel Table can’t be merged too. This is because sorting and filtering get affected if you merge cells in Excel. In this case, you have to convert the Excel table into a normal range. Then only you can centre the text.
Method 2: Center Across Selection
Some of the older versions of Excel did not contain the Merge and center option in the ribbon. This method 2 works when you are using such older versions,s but it also works in newer versions of Microsoft Excel, including Office 365.
And this method does not merge the cells. It only centers the text. Thus, our earlier problem with merged cells is actually eliminated.
To use this “Center across Selection” method, do the following steps:
Step 1: Select the multiple cells where the text is to be centered. The text should be present within this range.
Step 2: Click on Format Cells (Shortcut Control + 1 on Windows). Or right click the mouse button on the selection, and pick Format Cells from the Optionsmenu.
Step 3: On the Format Cells dialog box, stay on the Alignment Tab. In the Text alignment dropdown showing “Horizontal“, you will see the default alignment set to “General“. Pick “Center across Selection”from the selection. See screen shot below.
This will center the text across the selection. But the cells won’t be merged. This is the preferred way of centering a title in Excel.
How to Unmerge Cells in Excel
If you have clicked on Excel’s Merge and center text icon, and now decided to unmerge it, just select the text, and click on the “Merge and center” icon again. This button acts like a toggle switch. The merged cells will be unmerged, and the centered text will go back into the first unmerged single cell.
How to Align Text within a Single or Merged Excel Cell
If large cells, the text may appear at the bottom left corner. If you want to change the cell alignment vertically and horizontally, you need to click the Middle Align button & Centerbuttons under the Alignment group under Home Tab. Now the text will move to the center of the cell.
How to Center a Title in Excel Charts
When you create an Excel Chart, by default a Chart Title is added in the centre. In some old versions of Excel, you might not get the Title in the Chart. And in some cases, Excel can’t figure out what title to put, because the column name happens to be blank. In this case, you will end up without a chart title at all. But you can add a chart title by hand, manually and then center it.
Steps to Center the Chart Title in Excel
Click the Green+ icon on the top right of the Chart (applies to Excel 2013, 2016, 2019, Office 365 spreadsheets).
The Chart Elements will be displayed. Click the Checkbox on Chart Title. A Chart title is now placed in the top center of the Chart.
You can click inside the text box, and type any appropriate title text. It will be centered by default.
Print Excel worksheets with Worksheet titles in header or footer of the print out
Go to Page Layout.
Choose Page Setup popup from the bottom right flyout menu.
Choos Header/Footer tab from the Page Setup popup
Click Custom Header or Custom Footer option. Another Header or Footer Popup will open.
Click the Green Excel icon and the File name will be printed in the Left section.
Click inside the Right section, and then click on the page number # icon. The page number will now be printed in the right section.
You can display the current date in the Center section.
When you do a Print Preview, you will see a beautifully formatted Report, with the date centred, the file name on the left, and page number in the right section.
At ExcelChamp, we conduct a training course for Excel Formatting & Formula Tips & Tricks, which covers such formatting tips for beginner & advanced users alike.
Hope you enjoyed the tips presented here. They are pretty simple and easy to implement. Go ahead and amaze your audience with amazing charts, reports and analysis done in Excel.
Cheers, Vinai Prakash, Founder & Master Trainer at ExcelChamp.Net
Wondering How To Find the Age in Years from Date of Birth, in Microsoft Excel?
See multiple ways to calculate Age in Excel from any date of birth.
This simple, step-by-step tutorial on finding the age in Excel from date of birth or any other date, work on Microsoft Office 365, Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & Excel 2003 too.
We cover Microsoft Excel Functions & Formulas that allow you to calculate the following things in Excel:
Find the age in Years
Find the Age in Months
Find the Age in Days
Finding the exact age in Years, Months & Days.
Combining Years & Months to create a Text String the way we want.
Here’s how to calculate the number of years since the date of birth
Not only the years we can also calculate the months. I will show you multiple techniques to calculate the age in years, months & days.
If you want a whole number or you want the age as a fraction, there are so many ways to do it.
What we want to calculate is the age in years – something like Age is 55 years. Or Age is 55.56 years.
Before we start i want to highlight to you that the date of birth that you are using should be a date field so you can select it and make sure the formatting is not General or text, but it should be set as a short date or any kind of date.
To calculate the years, the formula is extremely simple. We will be using the DATEDIF function of Excel. =DATEDIF(DateofBirth, TODAY(), “Y”)
Similarly, we can calculate the age in months too.
=DATEDIF(DateofBirth, TODAY(), “M”)
This will result in the number of months from the date of birth to today’s date. You might see a huge number like 666 here.
But you may not want this. Perhaps we only want how many months after the 55 whole years have elapsed.
In this case, we modify the formula from only “M” to “YM”
=DATEDIF(DateofBirth, TODAY(), “YM”)
YM means it is only the months after the completed years. By doing this we can see that only six months have elapsed after 55 years.
Finally, we only need the days that have elapsed after the completed months in the age.
=DATEDIF(DateofBirth, TODAY(), “D”)
This will tell you that 20,000 days have passed from the date of birth, but, of course, we don’t want this.
So you change D to MD. The “MD” tells Excel to calculate how many days after the month have elapsed.
As you can see, we have the age calculated in different cells – the age is 55 years 6 months and 9 days exactly till today.
If you wish to see a complete combined date, you can use the CONCAT function to combine multiple formulas, or use the & to combine text as well.
As an example, we can write
=CONCAT(DATEDIF(dateofbirth,TODAY(),”Y”),” Years, “,DATEDIF(dateofbirth,TODAY(),”YM”),” Months & “,DATEDIF(dateofbirth,TODAY(),”MD”),” Days”)
For a DateofBirth date of 6-December-1966, it generates, 55 Years, 6 Months & 28 Days.
Hope you find this DATEDIF function quite handy for such use. Do give it a try and let us know how you use it.
One of the most popular Excel Lookup functions is VLOOKUP. Another Excel function to check out is the HLOOKUP function, which can do a horizontal lookup.
The newly added XLOOKUP is becoming very popular too. (The XLOOKUP function is currently only available in Office 365 versions).
For the advanced & power users of Excel, the mastery of Excel lookup functions like INDEX, MATCH & OFFSET can be considered vital. These functions are considered the “advanced” lookup functions in Excel.
The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel experts recently, asking if Excel’s VLOOKUP was overrated. I got a severe backlash for even mentioning it.
Almost everyone said that it is their GO TO function, an absolute must-have and that Excel won’t be that useable if this VLOOKUP function was taken away from Excel!
Most people swear by their VLOOKUP functions. It is their GO TO function when they want to lookup value of any type.
According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!
Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. This dictionary is the range of cells that contain the lookup up value, and its associated value. The V in VLOOKUP stands for the dictionary data array being a vertical dictionary. So for a vertical lookup you must use VLOOKUP function only.
=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)
In the VLOOKUP formula, the first column in the dictionary must contain the lookup up value, and the first row should be of the data. You should not include the headings in the dictionary table. The difficulty most people have with VLOOKUP is the last flag – the logical value of TRUE or FALSE(You can use 1 for True and 0 to indicate the False flag).
Once a matching value is found out, you will be able to get the return value based on the search. The error value of N/A will be generated if there is no exact match until the last row or the last column of the array.
The mystery is created because to use VLOOKUP for an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only. So for an exact match of a specific value, the last parameter is not really optional… it is mandatory.
Practical Examples of VLOOKUP:
= VLOOKUP(50, age_income, 2) will find the income from the second column of the age_income range for the age value of 50. Note that this is set as an approximate match lookup, as the last optional value is not specified.
=VLOOKUP(A5, $K$10:$L$100, 2, FALSE)
In this VLOOKUP example, we are looking for a piece of information from the column of data in the range $K$10 to $L$100. The search value is in cell A5, and the particular value of interest is in the lookup array that is set as an absolute reference.
The above example uses the reference functions that will generate an Exact match. If the correct value is not found, an error value of N/A will be received.
There are two major shortcomings in using the VLookup function of Excel.
First of all, the VLOOKUP is really a slow function. It is obvious when you do a lookup on a large list of 100,000 values or more.
Secondly, VLOOKUP can only look up a corresponding value from the columns on the right of the looked-up value. It can’t look to the left!
Make sure you master this Excel function really well.
HLOOKUP Function in Excel
An often forgotten cousin of the VLOOKUP formula, this Horizontal Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, a lookup dictionary is a horizontal dictionary of columns, denoted by the H.
HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of only16,000 columns. Whereas a list can grow vertically to over a million records easily.
In the following formula, this lookup function searches for the closest match, especially when we are not searching for an exact match, but an approximate match.
The dictionary is the table array and it is recommended that we use the absolute reference to lock the cells from moving.
=HLOOKUP(A5, $G$2:$K$100, 2)
Here the HLOOKUP will search for the exact or the next smallest value in the lookup table source data range of $G$2 to $K$100, and return the second row. If you want the third row, you can change the 2 into a 3.
Both VLOOKUP & HLOOKUP return single-cell values from a single row or a single column.
XLOOKUP Function in Excel
Did you know that new functions are added to Excel quite frequently, and these are extremely useful functions making approximate matches as well as exact matches?
Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function.
By default, XLOOKUP is set to do an exact match.
XLOOKUP requires a deeper understanding of the various scenarios it can handle. I’d recommend a dedicated video tutorial on XLOOKUP by Excel MVP Chandoo to learn how to use XLOOKUP in Excel.
This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.
If you know the row number, you can find the value on that row or column cell directly. INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2-dimensional array.
How To Use Excel MATCH Function
When you want to find an exact match in an array and return the row number in the array, MATCH comes to your rescue.
It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.
=MATCH(value, range_Lookup_Cells, FALSE)
This simple example MATCH function will look for the value in the range_Lookup_Cells, and return the row of the matched value exactly.
Index and Match can work on a single row or a single column of data too. These 2 lookup functions of Excel are the exact opposite of each other.
The index finds the value on a particular row, and Match finds the row or column number where the match happens to be found in the data range.
Both Index and Match can also run as an array form of the Excel Lookup Function.
Paired with INDEX, an INDEX & MATCH Function can manage to look up on the left or the right of any array of cells.
Microsoft Excel has become the easiest way to analyze data quickly. Several Summary functions, Pivot Tables, What-If Analysis, and other powerful features & methods are available for your use within Microsoft Excel right out of the box. Learn them, and your data analysis will become a breeze.
Here are the Top 10 Ways to Summarize Data in Excel Quickly
These data analysis & summarization tips will work in Microsoft Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & even Excel 2003. Further, they work on Excel for Windows as well as Microsoft Excel running on a Mac.
These Data Summarization Tips are listed in the order of the easiest to implement to the ones that need a bit more time. Some of the more complex data summarization methods will actually add more value to your data analysis.
Get The Data Ready For Summarization
Quick Summary With Auto Functions
Fast Analysis With Sort & Filter
Summarize Data With SubTotal Feature
Summarize Data With an Excel Table
Using Slicers to Summarize by different dimensions
Summarize With Excel Pivot Tables
Summarize Data With Excel Functions
Advanced Excel Functions for Summarizing Data
Summarize With Descriptive Statistics From Analysis Toolpak
You can apply the different ways to summarize data based on your familiarity with Excel.
The easiest methods of summarization are listed in the beginning.
And the Pivot Table technique is one of my favorite for a quick and dirty data summarization within Microsoft Excel. It always begins to give me numerous insights into the data.
We cover how to use the Pivot Table to Summarize Data in depth later in this article.
Let’s get started by exploring the different methods of summarizing data.
1. Get The Data Ready For Summarization
Before you begin your summarization, it is important to make sure that your original data is in a good shape.
Duplicate, a blank cell or missing values can often spoil your data summarization.
You need to make sure that the data range is correctly set up before you begin to analyze the data. Also, ensure there is no blank columns in between adjacent cells.
Ensure Proper Column Headings.
For each column, make sure that you have a short and unique column heading. Don’t leave any column without a heading, even though it may be obvious. Column Heading will make it easy to analyze data with any tool in Excel. This way the top row becomes the Header row.
Remove any Duplicates.
Duplicate rows can often sneak in from the data capture sources. So whether you capture data from the Web, or SalesForce, SAP or load from Text or CSV files, the first thing is to clean up the duplicates. To remove duplicates, click within the data range, and go to the Data Menu.
Data > Remove Duplicates.
That’s it, your data will now be cleaner.
Get Rid of Blank Rows:
While blank rows make the data look more readable and easier, it is a bane for data analysis. We do not want blanks to sneak in and skew our averages and other statistical calculations. By sorting the data with the different column headings, the blanks will get separated either to the top or to the bottom. Then you can simply delete these rows if they do not contain any other data points.
Don’t leave blank cells as blanks, specially if there is no value.
It is better to have a 0 than a blank value in any cell. For the text column, if the value is not known, it is better to have a NA (Not Available) showing up.
Having the data cleaned up is the first step in any data analysis. Now you can begin to apply the various data summarization methods.
2. Quick Summary With Auto Functions
The fastest way to summarize data is to calculate the Totals, count the number of entries, find out the average value, and figure out the highest and lowest values.
These 5 functions provide the vital stats of the data. These are the most basic and essential functions… just like a visit to a doctor starts with the nurse checking your vitals – height, weight and blood pressure.
These 5 numbers will provide a quick summary of your data. Here’s how to do this.
Here’s How To Create a Summary Section on Top of Your Data
Calculate SUM: Click on the Autosum icon on the Home tab of Microsoft Office to activate the Sum function of Excel. Then select the data range of the column you want to summarize. Here’s an example:
Calculate COUNT: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Count from the list. Then select the data range of the column you want to count. You can use the count function only for numeric columns like Salary, Sales, Quantity etc. using this function. So don’t try this on a text column like Country or Department.
Calculate AVERAGE: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Average from the list. Then select the data range of the column you want to Average. You can only average the numeric columns like Quantity, Profit, ROI etc. using this function. Here’s an example:
Calculate Highest Values: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Maximum from the list. Then select the data range of the column you want to choose for picking up the highest value. You can only pick numeric columns like Quantity, Profit, ROI etc. using this function. Here’s an example:
Calculate Lowest Values: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Minimum from the list. Then select the data range of the column you want to choose for picking up the lowest value. You can only pick numeric columns like Quantity, Profit, ROI etc. using this function.
Calculating the Sum, Count, Average, Min & Max are the fastest ways to get started in your journey to view summary data quickly.
3. Fast Analysis With Sort & Filter
If you sort the data by any key column like Country, Department, Category, Product, Zone, Area etc., then it becomes super easy to analyze data.
To sort the data on any column, follow these steps. Go to
Home > Sort & Filter > Sort A to Z or Sort Z to A.
This will sort the data in your chosen way (ascending or descending) for the column you are in.
Then click on the Filter icon. This will set up auto filters on top of all column headings. Now when you filter on any column, only values for that column will be visible. Unfiltered values are hidden, and visible data corresponds to the Filter selections.
After you have filtered for any particular selection, you can also highlight any numeric column, and see the summary being displayed at the bottom Excel bar.
At one time, you can see the Sum, Count, Average, Maximum & Minimum values from the selected range.
Now you can begin to summarize data for any selection, the way you want it.
4. Summarize Data With Subtotal Command:
If the data is already sorted, you are now ready to explore the Subtotal feature of Excel. This hidden gem has been languishing in Excel since the early 1990s, and very few people use it.
To add subtotals to your summary, first, clear all filters. And then Sort on the column that you want to add subtotal for.
The Subtotal feature is available on the Data tab of Excel.
Go to Data > Subtotal.
Choose the function to perform (Sum, Count, average etc), for the numeric column, and group by the desired column.
As an example, to get the subtotals of the number of employees by each department, it should say: At each change in Department, Count, number of Employees.
Click OK, and you should see the subtotal rows from the data in Excel. In the end, you will also see the Grand Totals calculated.
You can clear the subtotals, and perform the subtotals again at any time. To clear the subtotals, go to Data > Subtotal > Remove All. The subtotals and the Grand total row are all removed completely.
5. Summarize Data With an Excel Table
Microsoft Excel 2007 brought a new feature called Tables, which are amazing at doing simple summarization from a table of data.
To begin, stay within the data range on the Excel sheet. Then click
Home > Format as Table.
Select any colour you prefer, and click OK. Excel automatically recognizes whether the data selection has headers or not.
Now you have a new tab added to the Excel menu, at the end. It is called Table Design.
Select it, and check the Total Row checkbox. Now you have an additional total row added at the bottom of the data. Every column on the total row is a total calculator. Simply click on the dropdown showing in the total row cells, and choose an appropriate summarization – sum, count or average. Now that column will show a total sum or total count or total average of that column.
The great thing is that now if you filter the list, the totals will change dynamically. No need to call the Subtotal function every time. This technique of data summarization is better than the manual methods of subtotal.
Begin using the Table features if you haven’t done so already.
6. Using Slicers to Summarize by different dimensions
Beginning Excel 2010, Microsoft added the Slicers functionality which takes the Tables to greater heights.
Instead of filtering each column one by one, you can now create a slice of the data from the Excel spreadsheet at any time.
Go to Table Design > Insert Slicer > Pick the column of your choice that you want to filter on.
Now you can click on any value in the slicer, and the data is instantly filtered. You can make multiple selections, by holding the control key.
And more than 1 slicer can be added, giving you multiple combinations of slices. The total row updates automatically as each selection is made. This is truly slice and dice of data, allowing you to summarize data in Excel spreadsheets just by using a mouse. No formulas or lengthy steps are involved.
7. Summarize With Excel Pivot Tables
Pivot tables have been in Excel for over 30 years. They are the most used feature of Excel, as it allows for business analysts & managers to manipulate and analyze data in countless better ways.
A pivot table is actually a summary table, which allows you to slice and dice the data by different dimensions.
Pivot tables are easy to get started with.
To create a pivot table, Stay within your dataset. Then go to Insert > Pivot Table. The entire dataset is selected. Click OK and a new pivot table is created in a new worksheet by default.
Click on the column that you want to summarize on, from the pivot table field list on the right side of the screen. In our example, we check the Department and Sales amount from the field list. Instantly, the total sales per department are calculated and populated in the pivot table on the left. Notice that Excel generates a unique list of departments, without any duplicates.
To visualize both the Sum & Count of the data points, you can drag one more copy of the Sales into the Values Area section. Then right-click on the value, and choose
Summarize Values by > Count.
Similarly, you can change the calculation type, and pick up Average, Max, Min. This way, you can have all the vital statistics about your data summarized by each department.
Additionally, to see the percentage of sales done by each department,
Right Click on any numeric value in the pivot table, and choose Show Value As > % of Grand Total.
This will instantly calculate the % of contribution done by each department. The grand total will show 100% of the sales.
It is a good idea to sort the % of Grand Total value from highest to lowest by value, showing the highest values on the top.
You can then add a further selection in the rows or columns, to get a 3D view of your data. As you can see, Pivot Table is a powerful tool that can get the analysis done the fastest!
As you learn more about the Pivot Tables, you’ll realize that they can be used to summarize data from a single worksheet or multiple worksheets.
A pivot can even summarize data from multiple workbooks too. This is a must-learn feature of Microsoft Excel. You can attend an Advanced Excel Training in Singapore at Intellisoft, where I teach this class.
To get the most flexibility, you can actually write your own summary functions within Excel, by using the following formulas.
We have already covered the Autosum features of Excel which generate the Sum, Count & Average. Now we will look at how to write these functions manually.
To Sum a range of data, use =SUM(range) in the formula bar.
To Count a range of numeric data cells, use =COUNT(range). This generates a numeric count.
To count a range of alpha-numeric data cells, use =COUNTA(range). COUNTA can be used to count both numeric and non-numeric data.
To find the average of any data, use =AVERAGE(range).
9. Advanced Excel Functions for Summarizing Data
The real power of Excel functions is when you go beyond the basic summary functions and do some advanced data analysis.
You can use the following Excel formulas
Sum the data conditionally. For example, if we want to summarize the monthly sales only for a particular country, we can use the Excel Sumif function:
=SUMIF(country data range, select_country, monthly sales data range)
Similarly, to count the number of products sold by a country, we can count by using the COUNTIF function of Excel
=COUNTIF(country data range, select_country, monthly sales data range)
And average sales per country can be analyzed by using the following formula of AVERAGEIF.
=AVERAGEIF(country data range, select_country, monthly sales data range)
For multiple, if conditions, you can use the sumifs function to summarize data by your chosen selection. These functions can really cut down your data analysis time when you have large amounts of data to summarize.
10. Summarize With Descriptive Statistics From Analysis Toolpack
Finally, Microsoft Excel has the Data Analysis Toolpak, a hidden Statistical Analysis tool, that can calculate the Median, Standard Deviation, Variance, Analysis of variance (ANOVA), and much more in a single click.
To enable the Data Analysis feature in Excel, you must go to
File > Options > Add Ins.
Then select the Data Analysis ToolPak if it is inactive. You might have to click on the Go button at the bottom. Choose the Toolpak, and click OK. This will add a Data Analysis button on the Data tab of Excel, at the end. Check it out. Once this button is enabled, it stays active, and you can use it subsequently anytime.
To use this Data Analysis ToolPak, go to
Data Analysis > Descriptive Statistics.
Select the Entire numeric column that you want to analyze in the Input Range. Check the Labels in First Row checkbox if your data has a header.
Then check the output box radio button, and key in a cell address where you want the summary statistics to be generated. Check Summary Statistics, and click OK.
The full descriptive statistics are displayed instantly. This final result is a detailed Statistical Analysis of your data.
Multiple Ways to Summarize Data in Excel – Conclusion
There are so many different ways to summarize data in Excel. Mastering them will improve your data analysis skills, and you will be on your way to huge success, by taking action on the insights gleaned from your data analysis.
Go ahead and try them out.
Each technique is a gem, and adds to your skills in Excel data analysis.
You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)
Can’t Use LEFT Function
You can’t use a LEFT function, because a left function needs to know the number of characters you want to extract. And this itself is variable… There are 2 characters in HQ, 6 in London, 7 in Houston etc.
One of the ways I have discovered is to find the location of the first Hyphen (-), and extract all characters from the left of it.
Finding the location of the First Hyphen We can use the FIND function to find the location of the first hyphen.
If A2 contains HQ-1022-PORT, we can use the formula as:
The answer would be 3. This means that the hyphen is the third character in the string. This is perfect. Now we know that we need n-1, that means 3-1, which is 2. We need 2 characters from the Left of this number.
So we can write another formula as
=LEFT(A2, FIND(“-“,A2)-1 )
The resulting answer would be HQ. Copy the formula down to other cells, and you should be able to extract HOUSTON, SINGAPORE etc. without much hassle, and without having to write another, different formula.
One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!
Do you have any other method? How would you solve this challenge?
Please post your answers in the comments below.
P.S. – I am teaching a 2 day course on Data Interpretation & Analysis in Singapore on Feb 9-10, 2015, and another on March 9-10, 2015. If you would like to learn techniques to analyze data and create management reports, you can attend this program.
Awaiting you and your innovative comments below … 🙂 – Vinai
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!