Excel tips

I posted this as a Pro Tip two weeks ago, but, for some reason, it doesn't show up in the pro tip section. Apologies to those who've seen it already.* By default, Excel inserts relative references (e.g. A1), which is great for copy/pasting and dragging, but sometimes you want to lock the column or row of the reference, or both. You do this by adding a $ in from of the part of the reference you want to lock (e.g. $A1 will lock the column).Use the F4 key on Windows while editing a formula to toggle between relative and absolute as illustrated here: https://imgur.com/a/wEsvrMac users: Command + T or Fn+F4 on Excel for Mac 2016 (according to Exceljet)

So I learnt something new today, and wanted to share the same.I was pulling out data from a pivot, but I needed the values in millions. I was manually converting the vales into millions by dividing it.I learnt, one can change the number format, in the custom format like this:#,##0.0,,Note the 2 commas after the format - this scales the number down by a factor of a thousand for each comma. More info and explanation on this here.

The COUNT formula in Excel is denoted =COUNT(Start Cell:End Cell). This formula will return a value that is equal to the number of entries found within your desired range of cells. For example, if there are eight cells with entered values between A1 and A10, =COUNT(A1:A10) will return a value of 8. The COUNT formula in Excel is particularly useful for large spreadsheets, wherein you want to see how many cells contain actual entries. Don't be fooled: This formula won't do any math on the values of the cells themselves. This formula is simply to find out how many cells in a selected range are occupied with something. Using the formula in bold above, you can easily run a count of active cells in your spreadsheet. The result will look a little something like this:

An array formula in Excel surrounds a simple formula in brace characters using the format, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}. By pressing ctrl+shift+center, this will calculate and return value from multiple ranges, rather than just individual cells added to or multiplied by one another. Calculating the sum, product, or quotient of individual cells is easy -- just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells? Numerical arrays are a useful way to perform more than one formula at the same time in a single cell so you can see one final sum, difference, product, or quotient. If you're looking to find total sales revenue from several sold units, for example, the array formula in Excel is perfect for you. Here's how you'd do it:

• To start using the array formula, type "=SUM," and in parentheses, enter the first of two (or three, or four) ranges of cells you'd like to multiply together. Here's what your progress might look like: =SUM(C2:C5
• Next, add an asterisk after the last cell of the first range you included in your formula. This stands for multiplication. Following this asterisk, enter your second range of cells. You'll be multiplying this second range of cells by the first. Your progress in this formula should now look like this: =SUM(C2:C5*D2:D5)
• Ready to press Enter? Not so fast ... Because this formula is so complicated, Excel reserves a different keyboard command for arrays. Once you've closed the parentheses on your array formula, press Ctrl+Shift+Enter. This will recognize your formula as an array, wrapping your formula in brace characters and successfully returning your product of both ranges combined. In revenue calculations, this can cut down on your time and effort significantly. See the final formula in the screenshot above.

The Excel DATE formula is denoted =DATE(year, month, day). This formula will return a date that corresponds to the values entered in the parentheses -- even values referred from other cells. For example, if A1 was 2018, B1 was 7, and C1 was 11, =DATE(A1,B1,C1) would return 7/11/2018. Creating dates in the cells of an Excel spreadsheet can be a fickle task every now and then. Luckily, there's a handy formula to make formatting your dates easy. There are two ways to use this formula:

• Create dates from a series of cell values. To do this, highlight an empty cell, enter "=DATE," and in parentheses, enter the cells whose values create your desired date -- starting with the year, then the month number, then the day. The final format should look like this: =DATE(year, month, day). See how this looks in the screenshot below.
• Automatically set today's date. To do this, highlight an empty cell and enter the following string of text: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())). Pressing enter will return the current date you're working in your Excel spreadsheet. In either usage of Excel's date formula, your returned date should be in the form of "mm/dd/yy" -- unless your Excel program is formatted differently.

To perform the division formula in Excel, enter the cells you're dividing in the format, =A1/B1. This formula uses a forward slash, "/," to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5. Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, "=," and follow it up with the two (or more) values you'd like to divide with a forward slash, "/," in between. The result should be in the following format: =B2/A2, as shown in the screenshot below. Hit Enter, and your desired quotient should appear in the cell you initially highlighted.