Interesting Microsoft Excel tips and problem solving.
Presented below is our Microsoft Excel tips that will save you time and effort in your everyday Excel activities.
When dragging and filling down a worksheet, everybody knows to left mouse click hold on the bottom right-hand side dot (i.e. fill handle) of a range selection and drag. Depending upon the contents of the selection, Excel will fill a series of the values when possible or copy / paste down the drag selection. However next time, after selecting the range to drag, right mouse click hold over the fill handle and drag the selection. When you release the mouse, a short cut menu will appear prompting you with options. This will depend upon the contents of the cell. Pictured below, a single date in a cell, a serial date number, was dragged down the worksheet using the right mouse button. At the end of the drag and releasing the mouse button, options appear to ask you how you want to sequence the date. Very handy.
Right mouse click hold over fill handle and drag. Select from shortcut menu.
When performing formula calculations in Excel, many times the output of a formula part is a piece of text. For example when using the RIGHT worksheet function to extract text from the right-hand side of a string like =RIGHT("CX100",3), it will return "100" as an answer. Numbers as text can wreak havoc on things like Pivot Tables and other functions that might use the value like VLOOKUP, MATCH and so forth. The idea here is to develop a simple automated method to modify the values without having to manually do it as an extra step or use an extra function. These concepts are illustrated below in a Pivot Table trying to read numbers output as text that are not modified.
The answer to the problem is actually very simple. Excel formulas, not function calls, just basic formulas like =A1+A2 have the ability to coerce a number entered as text into a number if the text number is used in a math operation. For example, If cell A1 has "100" in it and cell A2 has 200 in it, then developing a formula in A3 like =A1+A2 will automatically flip the value in cell A1 to a number and add it. They call this data type coercion in computer programming and Excel has it built in, who knew. This concept is illustrated below in the simple formula that flips the value in cell A1 before using it.
So to flip a text output in a formula without changing its value, simple multiply the output by 1 and you have your number back. It works on date text as well number text. You can test out different things. Using this method allows you to automatically rotate text to numbers without doing any other steps in Excel or using extra functions. This concept is illustrated below in a formula that takes a bad text date, removes its periods and flips its value to a serial number and also in a formula that returns the right 3 characters from a piece of text and flips it to a number.
Microsoft Excel has the ability to perform calculations on a worksheet in addition to just storing data in cells. To perform a calculation on a worksheet, you enter a formula in a cell. A cell is an intersection between a worksheet row and column and are referred to as A1, B2,...etc.
To enter a formula, first open a workbook and select a worksheet. Next type the values in the cells the formula will use on the same worksheet you will type the formula (multi-worksheet formulas later tip). Just click on a cell, type, and press Enter. When entering the values, think like using a calculator, the numbers you punch into a calculator are the numbers you put on the worksheet. Also group the numbers together on the worksheet so users can easily find and type in them. Format the area if you can which is next week's tip. Type text to the left of the numbers so users know what they are typing. When typing in the numbers, dollar signs do not matter to Excel. They are called a format and can be changed at will, also next week's tip. These concepts are illustrated below.
After entering the values, select a cell to place the formula in. To enter a formula in the cell, press the = sign. A = sign always starts a formula in a cell. In this example, we are going to add the expenses so click on the Hotel expense with the mouse, type a +, click on the Car expense with the mouse, type a + and last click on the Flight expense and press Enter. Always press Enter to enter a formula, do not click off the cell with the mouse. These concepts are illustrated below.
As you just noticed, you use cell references in a formula to read the cell values used in the formula. It is just simple substitution, where you see B2, visualize 520.75 and you have the concept. A mathematical operator (i.e. +, -,...) is used to separate the cell references in the formula and perform the math. You never end a formula with a math operator like + or -. Also, where the formula is placed on the worksheet is not important although you should place it where users can easily see it.
The real power behind using formulas in Excel is if you change a cell value, the formula recalculates. Formulas can be used to calculate everything from basic tasks to Mach 2 fighter jets. No matter how high tech the formula is, they all use the same basic principles shown here.
To edit a formula, double click on the cell, click in the cell, edit the cell references and operators and press Enter. You will notice the cells used in the formula will be color coded so you can easily figure them out.