These are a few of the highlights covered in Part 3 of our 3-Part Webinar Series on Microsoft Excel for our Baton Rouge IT customers: Excel Like a Pro - Part 3.
Why is it beneficial to name cells in an Excel spreadsheet? It is easier to recognize and use the name of a cell instead of its given cell reference. For example, if cells A1:A50 represent sales values by naming this range of cells “Sales” it provides an absolute reference to those cells. Named ranges make your formulas much easier to understand and maintain as well as provide shortcuts to quickly go to those cells within your worksheet. Use the Name Manager dialog box to work with all the defined names in a workbook.
You have probably had experience with the most common statistical functions in Excel such as Average and Count. But there will be times in Excel calculations when you might need to add certain criteria to these functions to achieve a specific outcome. In these cases, you would use the AVERAGEIF or COUNTIF functions. The AVERAGEIF function returns the average (arithmetic mean) of all the cells in a range that meet a given criteria (if). For example, suppose you have a worksheet listing sales by region and you want to see the average sales but only of the southern region. The COUNTIF function returns the count of all the cells in a range that meet a criterion. For example, suppose again in your worksheet listing of sales by region you want to see the number count of sales but only within the southern region. Both the AVERAGEIF and COUNTIF functions work with numeric and text values, as well as with date values.
There will be times in Excel when you want to auto populate cell data entry based on other associated cell values within a range. Use the LOOKUP function, one of the Lookup and Reference functions, when you need to look in a single row or column and find a value from the same position in another row or column. For example, suppose you have a worksheet listing auto parts and you know the part number for an auto part, but you do not know the price. You can use the LOOKUP function to return the price in cell A2 when you enter the auto part number in cell A1.
Although you may think an Excel worksheet is primarily used to track number values, it is quite often used to list text values. And when you need to combine text values into one cell, you have the popular text function CONCATENATE at your disposal. The reason the CONCATENATE function is so popular is due to its versatility. For example, suppose you have an Excel worksheet listing contact information. For more efficient sorting and filtering, it is not recommended to combine name values into one cell but to separate them individually. You can then use the CONCATENATE function to combine first and last names separated with a comma if needed.
Have you possibly been tasked with creating a departmental budget or petty cash log? Excel has a template for that! Get a head start on creating one of these worksheets just by searching in the Excel File menu and downloading one of these templates for your use. To ensure that your template will always be available in your File menu, make sure to save it as an Excel Template file format.
This and a lot more was covered in our webinar Excel Like a Pro - Part 3.
