Category: MS Excel Formulas

Excel has many functions to offer when it comes to manipulating text strings. Join Strings To join strings, use the & operator. Note: to insert a space, use ” ” Left To extract the leftmost characters from a string, use the LEFT function. Right To extract the rightmost characters from a string, use the RIGHT function. Mid To extract a substring, starting in the middle of a string, use the MID function. Note: started at position 5 (p) with length 3. Len To get the length of a string, use the LEN…

To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the “:” (colon). You can also enter a date and a time in one cell. Year, Month, Day To get the year of a date, use the YEAR function. Note: use the MONTH and DAY function to get the month and day of a date. Date Function 1. To add a number of days to a date, use the following simple formula. 2. To add a number of years, months and/or days, use the DATE function. Note:…

Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success. Relative Reference By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative. 1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5. Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and…

Learn how to use Excel’s logical functions such as the IF, AND and OR function. If Function The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE. 1. Select cell C1 and enter the following function. The IF function returns Correct because the value in cell A1 is higher than 10. And Function The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false. 1. Select cell D1 and enter the following formula. The AND function returns…

The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria. Count To count the number of cells that contain numbers, use the COUNT function. Countif To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function. Countif To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function. Sum To sum a range of cells, use the SUM function. Sumif To sum cells based on one criteria (for…

Encrypt an Excel file with a password so that it requires a password to open it. 1. Open a workbook. 2. On the File tab, click Save As. 3. Click Browse. 4. Click on the Tools button and click General Options. 5. In the Password to open box, enter a password and click OK. 6. Reenter the password and click OK. Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered. 7. Enter a file name and click Save. It requires a password to open…

Learn how to share Excel data with Word documents and other files. Paste Most of the time, you’ll simply need to paste static Excel data in a Word document. 1. Select the Excel data. 2. Right click, and then click Copy (or press CTRL + c). 3. Open a Word document. 4. Press CTRL + v. 5. Click the icon in the upper left corner of the table and add borders. Result. Note: To delete the table, right click the icon in the upper left corner, and then click Delete Table. Paste…

This chapter teaches you how to print a worksheet and how to change some important print settings in Excel. Print a Worksheet To print a worksheet in Excel, execute the following steps. 1. On the File tab, click Print. 2. To preview the other pages that will be printed, click ‘Next Page’ or ‘Previous Page’ at the bottom of the window. 3. To print the worksheet, click the big Print button. What to Print Instead of printing the entire worksheet, you can also only print the current selection. 1. First, select the range of cells…