SUM Formula The SUM Function is an expedient way to add the values within a column or row or even non-adjacent cells. SUM is found within the Functions Tab under Math & Trig, scroll down the menu and select SUM. AutoSum is also located as its own button in the Functions Tab (highlighted). The third location for the SUM Function is in the Home Tab in the editing box. The fact that Excel has [...]

What does it do? Syntax explained =INDIRECT(ref_text,[a1]) Above you’ll see the syntax in Excel language, the part in brackets is where the magic happens so let’s break these both down and explain what each element does, but before we do, let’s explain what the term A1 and R1C1 style mean.  This will affect the way you format this formula and also applies to other areas in Excel so it’s important to understand it’s relevance.  A lot [...]

What does it do?In brief summary, its a test of one number vs another, in it’s basic for this test will output a TRUE or FALSE answer.  This basic function can be used to integrate into other Excel function such as SUMIF and IF functions to name but two, we will look at these examples below. As we’ll be looking at COUNTIF and IF see below, please refer to those articles for a full run [...]

OR, AND, NOT Functions in Excel The OR Function is found in the Formulas Tab under Logical. It checks whether any of the arguments are true and will yield either a TRUE or FALSE value. 5. All employees who have a TRUE will get the Sales Bonus this year The syntax or formula is =OR(logical1,logical2,logical3,…) The OR Function can be used with Logical Operators, nested within an IF Function, and used in conjunction with [...]

In Excel, the Mean is referred to as the Average. To find the mean of a set of numbers you add all numbers within the group and divide the total by the count of those numbers. In the business sector, the mean is the most commonly used measure to find the average of a set of values. Take Sales for example, it’s not uncommon to use the average sales from a previous year to predict future [...]

What does it do?In brief summary, the Excel HLOOKUP function looks for a given criteria in the first row of a range and once found it looks in that column and at row ‘x’ in your range.  It’s almost identical to VLOOKUP, but the difference being VLOOKUP is V for Vertical (Columns) and HLOOKUP is H for Horizontal (Rows). Syntax explained=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])If you know VLOOKUPS then you’ll grasp HLOOKUP in minutes.  It all [...]

This Element within Excel allows others to enter or edit information into all other cells except for those which have been locked. Hiding or Locking Cells has no effect at all on the worksheet until you protect the worksheet.When sharing projects among several team members sometimes it is necessary to lock certain cells within the worksheet to ensure the data isn’t deleted or changed. When the worksheet is shared, the team member will be able [...]

AbstractYou will often receive spreadsheets with usernames. Sometimes these spreadsheets have the first name and the second name in different columns. However, spreadsheets usually contain the Full name in a single column. So what do you do then? Simply follow the options mentioned below to split the user name as first name and last name into two columns.Get first name and last name through the GUILevel of difficulty: LOWHow To Separate First And Last Name [...]

AbstractMany times, data is entered into Excel workbooks with spaces. These unwanted spaces might be at the beginning of the data or at the end or even sometimes in between strings. These spaces can cause issues when you try to analyze the data and create reports out of it. The following options will help you remove unwanted spaces from your Excel sheets.Remove spaces through the GUILevel of difficulty: LOWHow To Remove Spaces In Excel Find and [...]

Abstract Excel workbooks might contain sensitive information like bank account numbers, employee address, social security numbers, critical weekly reports or company’s financial reports. So, it is crucial to protect your Excel workbook.In this article, you would learn to protect an excel file with a password. This can be achieved by using the native GUI method or a macro. So, now let’s see how this is done. Password protect through the GUI Level of difficulty: LOW [...]