Truncate Text Strings in Excel
When data is imported or copied into a spreadsheet unwanted characters or words can sometimes be included with the new data.
Excel has several text functions that can be used to remove these unwanted characters.
The LEFT function is one such example. It allows you to specify how many characters in a cell you wish to keep and the rest are removed.
To find out the details of using this function read the short tutorial on how to Truncate Text Strings in Excel with the LEFT Function.
Related Tutorials
Combine Text Entries in Excel
Excel CONCATENATE Function
© Ted French
Excel's CONCATENATE function can be used to combine data from multiple cells in a spreadsheet.
Names and addresses are often split between two or more cells in an Excel database. Using the CONCATENATE function these can be combined into one cell when needed.
The data can be words, cell references, blank spaces, or numbers.
To find out all the details read this short tutorial on the Excel's CONCATENATE Function.
Related TutorialsAdd Cells to an Excel Worksheet
Add Cells in Excel
© Ted French
When creating a spreadsheet, the layout I start with is usually not the layout I finish with. Having planned my spreadsheet, I always think I have a pretty good idea of where things will go.
Before long, however, I realize that I left out this data or forgot to leave room for that formula
So I often end up adjusting the layout by adding extra cells to the worksheet.
Fortunately, this isn't difficult to do since cells can be added in Excel with a couple clicks of the mouse.
To find out just how easy it is, read this short tutorial on how to Add Cells to an Excel Worksheet.
Related Tutorials
Excel AVERAGEIFS Function
Like the SUMIF and SUMIFS functions below, there is also an AVERAGEIF and AVERAGEIFS functions in Excel.
The difference between these two functions that combine the AVERAGE and IF functions is the number of criteria that must be matched before a data record is included in the average.
AVERAGEIF looks to match a single criterion while AVERAGEIFS can be used to match from two to 127 criteria ranges.
AVERAGEIFS could be used to pick out data for specific years - allowing you, for example, to average only the profits for the years 2007 and 2009 from a worksheet containing data for the years 2006 to 2010.
To find out more, read the step by step tutorial on the Excel AVERAGEIFS Function.
Related Step by Step Tutorials
Excel COUNTIF Function
Excel COUNTIF Function © Ted French
Excel's COUNTIF function combines two other commonly used functions - the COUNT function and the IF function.
The result is that COUNTIF will only count cells containing data that meet a condition that you have specified.
COUNTIF can be used for such things as finding out how many customers have made purchases in the past month, or how many employees work in the East sales region.
To find out the details of using this function read the Excel COUNTIF Function Step by Step Tutorial.Related Tutorials
Excel SUMIFS Function
The function can be used to pick out data for specific years - allowing you, for example, to add up only the profits for the year 2006 and 2007 from a worksheet containing data from 2004 to 2008.
To find out the details, read the step by step tutorial on using the Excel SUMIFS Function.
Related Step by Step Tutorials
Excel SUMIF Function
Two of the most useful functions available in Excel are the IF Function and the SUM Function:
- The IF function introduces decision making which adds flexibility to your to spreadsheets.
- The SUM function makes it easy to add up data located in rows or columns.
So useful are these two functions, that they have been combined into one - The SUMIF function.
SUMIF will add up values if a certain criterion is met. You get to specify the criterion to be used and also the data to be summed.
To find out more, read this step by step tutorial on the Excel SUMIF Function.
Related Step by Step TutorialsExcel TRUNC Function
The TRUNC function in Excel is similar to the
INT function in that they both remove all decimal places from a number leaving only the whole number.
How they differs is that while the INT function rounds the remaining whole number down, the TRUNC function does no rounding at all.
Removing extra decimal places can make it easier for yourself or others to read and understand the data.
To find out how to use this function, read the short tutorial on the Excel TRUNC Function.
More Rounding Number FunctionsExcel IF Function Step by Step Tutorial
The Excel IF function adds flexibility to your spreadsheets by introducing decision making. It does this by testing a specified condition in your spreadsheet to see if it is true or false.
If the condition is true, the function will carry out one action. If the condition is false, it will carry out a different action.
The function allows you to specify what actions it should carry out depending on whether the condition is true or not. These actions can include executing a formula, inserting a text statement, or leaving the target cell blank.
To find out more about how this function works, read the step by step tutorial on the Excel IF Function.
Related TutorialsRounding Numbers with the CEILING Function
The CEILING function in Excel is similar to the ROUNDUP Function in that they both round the last digit upwards.
How they differs is that while the ROUNDUP function only ever rounds that last digit up by one number, the CEILING function can round up by multiples of the last digit.
Rounding by multiples allows you to set the level of significance for the remaining digits.
To find out all the details on using this function, read this short tutorial on Excel's CEILING Function.
More Rounding Number Functions