Spreadsheets

The SWITCH() function in Google Sheets is handy when dealing with multiple criteria based on a single result. The SWITCH() function takes at least 3 parameters, with the first parameter being the value to evaluate, the second and third parameter representing a pair of combined cases and values. There’s also an optional final parameter that acts as the default result if no cases are satisfied. Here is a simple demonstration of the SWITCH function: ...

What is the formula to add in Excel? There are two popular ways to add numbers together in Excel: using the plus sign + or using the SUM formula. Use the + sign where values being added are manually entered, and use SUM when referencing specific cell values. Here are some examples demonstrating the use of each type: When To Use + (Plus) Sign In a spreadsheet the plus sign can be used in the same way you use a calculator. However, to get the spreadsheet to begin adding the numbers you must prompt the cell by entering an equal sign =. This triggers the spreadsheet to know that it will need to do a calculation with what you’re about to enter. ...

If a cell contains words it can be easy to split these into individual cells using the Text to columns feature in Excel. Simply select the cells you want to split into multiple columns, navigate to the Data menu then click on the Text to Columns button. From this Text Wizard window select Delimited width (click Next >), then set the delimiter type to Space (click Next >) then click Finish. ...

When you start entering text into a cell and the first character of that cell is a plus symbol (+) you will get an error #NAME? and you would have noticed the cell changed to =+A. So how can you just display a cell with a plus sign? A 1 #NAME? =+A Result after starting cell with + To display a cell that starts with a plus sign (+) enter the symbol that lets the spreadsheet know you’re entering text by starting with the single apostrophe (') then entering your plus symbol and whatever ever text next. For example, your cell would now contain '+A. ...

When using large numbers in Excel or any other spreadsheet application, such as Google Sheets, some cells may display a number in scientific notation like 9.991E+35. How do you get rid of that E+n bit, where n is some number, in the cell? A 1 9.997E+11 =9999^3 Large number in cell The easiest way to change a number being displayed as E+n (where n is a number) is to right-click on the cell, click on the context menu item Format cells and then in the Number tab and the Category section select the option of Number. ...

The VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this: = V L O O K U P ( r e f e r e n c e _ v a l u e , d a t a , r e t u r n i n g _ c o l u m n _ v a l u e , [ i s _ s o r t e d ] ) The first parameter reference_value is the value you are searching for in the first column of your data set (the second parameter). The third parameter returning_column_value is the column from your data set that you wish to return, and the optional fourth parameter is_sorted is a boolean type where if this is true (by default it is) then the returned value from this formula will be its first match, otherwise if FALSE then returns the closest match. ...

Can you use noncontiguous ranges in the data parameter in a QUERY() function? Yes, you can. Google Sheet’s QUERY() function permits the ability to use noncontiguous ranges, but they must be wrapped in set notation with curly braces {}. When applying a filter in the query parameter, you will need to use the ColX notation to reference the specific range according to its order in the set. Recall that the QUERY formula contains a total of three parameters as follows: ...

Merged cells are a great way to span content over multiple cells, and you can easily remove a single merged cell with one click by clicking on the merge cell button, but unfortunately, you cannot apply the same process when trying to unmerge a whole array of rows or columns that contain cells with multiple merges. For example, have a look at the following spreadsheet which contains a multiplicity of rows all containing 3 merged cells: ...

There will come a time when using Google Sheets where you will be checking the value of a cell against multiple criteria, which one is the best to use? There are three handy functions you can use within a single cell to handle multiple conditions: IF, IFS and SWITCH. Let’s examine each formula individually and how they might fit your needed case. IF Formula The IF formula is the simplest and probably one of the first formulas most users of spreadsheets start with. Its a formula that contains three parameters: ...

Recently I had a column of names in a spreadsheet with the following structure: Last Name, First Name and they needed to change to the structure: First Name Last Name. For example, the original structure of someone’s name would be Smith, John and this needed to change to John Smith. So a couple of medications were needed. First, fetch the respective names and identify them positionally according to their place on either side of the comma. Then, remove the comma and concatenate the names using a space character to separate them. ...