Tips to increase your proficiency in Microsoft Excel
New Functions in Excel 365
Excel 365 has added six new functions
TEXTJOIN - Instead of using CONCATENATE, you can use the new TEXTJOIN function
With CONCATENATE the function may look like this - =CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)
With TEXTJOIN, you would use - =TEXTJOIN(“, “, TRUE, A3:E3)
CONCAT - Instead of CONCATENATE, you can use CONCAT
IFS - The new IFS function accepts multiple logical tests
=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1<60, “Fail”)
SWITCH - The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned
If A2=2, and Monday is the result argument corresponding to the value 2, SWITCH returns Monday
MAXIFS and MINIFS - Allow multiple conditions for your MAX or MIN results
Click HERE to see more information on these new functions
Quick Date Entry
To insert the current date into a cell, press CTRL+; (control key plus the semi-colon key). To insert the current time, press CTRL+SHIFT+;
Instead of clicking the auto sum button , press ALT+= (Alt key plus the equals key).
AutoFill in reverse
You already know how to use AutoFill. If not, type January in a cell. With that cell selected, place your insertion point over the lower right corner, on the fill handle, until it turns into a black cross. Click, hold the left mouse button down, and drag down a few cells. When you release the mouse, you will see the cells filled with the appropriate months. However, try it in reverse and you will remove your AutoFill. With the same cells, go to the lower right corner and reverse the procedure by dragging into the listed months. You will see them disappear.
To place a border around a cell or a selection range, press CTRL-SHIFT-&.
Stay in the cell
Instead of going to the cell below when you press the Enter key, press CTRL-Enter and you will stay in the same cell.
Get to the End
Want to go to the last column with data in a range in an Excel worksheet. Press CTRL-right arrow key. Try CTRL-down arrow key for quickly getting to the last row with data in a range. Return to cell A1 by pressing CTRL-Home.
Hide Excel Values
Sometimes you may want to hide values in individual cells. You can do this by assigning a null number format to the cells. To do so, select the appropriate cells. Then, choose Home - Numbers - General, More Number Formats. Select Custom from the Category list box. Then, in the Type text box, enter three semicolons (;;;). Finally, click OK.
The Underline button does double-duty
If you've ever wanted to double underline the contents of a cell, you probably selected the dialog launcher from the Font group, clicked the Underline drop-down list, and clicked OK. That's quite a bit of work for such a minor formatting change. You can achieve the same results simply by pressing the [Shift] key and then clicking the Underline icon on the toolbar.
Easily re-order worksheet columns
You've probably run into situations in which you need to re-arrange the order of the columns in a worksheet. However, you may not be going about it in the most efficient manner. Rather than enduring the laborious process of inserting a blank column and then cutting and pasting the data, there's a much easier way. First, select the columns you want to move. Move your mouse pointer to the edge of the selection until it changes from a cross to a regular pointer arrow. Now, press [Shift], then click and drag the column to the new position. You'll see a faint "I" bar that runs the entire length of the column, along with a box indicating what the new address will be. Simply release the mouse button before releasing the [Shift] key, and the column is moved without overwriting any data. Note that the same basic technique also works with rows.
Easily align objects to Excel's columns and rows
If you're having a hard time aligning objects on a worksheet, try this shortcut. Hold down the [Alt] key when dragging a chart or graphic, and the object's frame will snap to the cell.
Select Current Range
A quick way of selecting the current range is to press CTRL-*. (The asterisk is on the number pad, or on a laptop, the keystroke is CTRL-SHIFT-8 on the top row of numbers). This will select the current range in which the insertion point is located.
Allan Bach, Consultant - Hellertown, PA - 610-248-0237 - firstname.lastname@example.org