Posts

Examples To retrieve a field value from a linked data type, provide the field name as text in double quotes (""). For example, with a city in cell A1, linked to a Geography data type, you can request population data like this: In the example shown, the formula in cell C5, copied down, is: The result is population data for the 12 cities listed in B5 to B16. Alternative syntax The FIELDVALUE function is an alternative the “dot” syntax for retrieving a field value from a data type....

The INT function takes just one argument, number, which should be a numeric value. INT returns a #VALUE! error if number is not a numeric value. If number is already a whole number, INT has no effect. Examples When numbers are positive, the INT function rounds down to the next lowest whole number: Notice INT rounds positive numbers down toward zero. With negative numbers, INT rounds down away from zero:...

The LCM function takes one or more arguments called number1, number2, number3, etc. All numeric values are expected to be integers. Numbers with decimal values will be truncated to integers before a result is calculated. Each argument can be a hardcoded constant, a cell reference, or a range that contains multiple values. The LCM function can accept up to 255 arguments total. Examples The least common multiple of 3 and 4 is 12, since 12 is the smallest multiple of both 3 and 4: The least common multiple of 3, 4, and 5 is 60, since 60 is the smallest multiple of all three numbers:...

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

(1) Move the cursor where you want to break the line (2) Type Alt + Enter (3) Make sure “wrap text"is enabled to see lines wrap in cell: Notes Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Conflicts with Mac Universal Access Zoom. System preferences > Keyboard > Universal Access > Zoom out Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

After a few problems, you might wind up thinking that Mac shortcuts are “totally different” or somehow “broken”. In reality, Excel shortcuts on the Mac are quite capable, you just have to understand and adjust to certain differences. In this article, I’ll walk you through the key differences you need to be aware of to work productively with Excel shortcuts on a Mac. 1. Special symbols One of more confusing aspects of keyboard shortcuts on the Mac are the symbols you’ll see for certain keys....

The same shortcuts used by Excel pros Work faster and more efficiently Study offline at your leisure Compare Windows and Mac shortcuts Free instant access

In the example shown, the formula in D5 is: About z-scores / standard scores A z-score, or standard score, is a way of standardizing scores on the same scale by dividing a score’s deviation by the standard deviation in a data set. The result is a standard score, or a z-score. It measures the number of standard deviations a given data point is from the mean. A z-score can be negative or positive....

For example, if you have similar data in B2:B11 and C2:C11, and you want to highlight cells where values differ, select the data in both columns, starting from B2, and use this formula: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B2 in this case. The references to $B2 and $C2 are “mixed” - the column is locked, but the row is relative - so only the row number will change as the formula is evaluated....

Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B5 in this case. This formula refers to two named ranges: start (C2) and end (E2). References to the start and end dates (C2 and E2) are absolute and will not change. References to the date in column C are “mixed” — the column is locked, but the row number is free to change....

Where A1 represents the active cell in the selection when the rule was defined. The CELL function returns either 1 or zero to indicate “on” or “off”. In this case we are comparing the result to zero, so when CELL returns 0, the expression returns TRUE and the conditional formatting is triggered. When CELL returns 1, the expression returns FALSE and no conditional formatting is applied. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

Here we have some website data that includes orders and the conversion rates for five different channels. Let me first insert a column chart to plot orders per channel. Now, let’s say I’d like to also add the conversion rate into this chart. I can easily do that by selecting the chart and dragging the data area to include conversion. But now we see a problem. The conversion rate is measured as a percentage, where all numbers are less than 1, while the orders are much bigger numbers....

Let’s take a look. The General format has just two basic rules. The first rule is that if a number contains decimal places, they will be displayed up to the number that fits inside the column. When the column width is increased or decreased, Excel will adjust the number of visible decimal places automatically. If the width of a column is decreased, the number will be rounded as needed. The actual cell values remain unchanged....

Let’s create pivot tables to answer these questions about our sales data. What were the total sales for each sales representative?What were the top 3 States for sales?What were the total sales for each region by quarter?How many of orders were there for each product? The great thing with pivot tables is it’s easy to answer questions like these about your data with just a few drag and drop actions....

The default row height in Excel is determined by the font size. As you increase or decrease the font size, Excel will adjust the row height to fit. However, you can still adjust the row height manually. Let’s take a look. All rows in Excel have a set height. To check on the current height of a row, just click the row divider at the bottom. You’ll see the height displayed in points....

A common example of a situation that requires concatenation is assembling a mailing address from data in separate columns. If I want to create a mailing address using this data, I need to create a formula that uses CONCATENATION to bring the name, street, city, state and zip together. In addition to cell references, I also need to include “literal text” for spaces and a comma. This works, but notice that everything just ends up on the same line....