Posts

Download Example File Watch Video – Two-variable Data Table in Excel One Variable Data Table in Excel. Scenario Manager in Excel. Goal Seek in Excel. Excel Solver. Two-variable data table is best suited in situations when you want to see how the final result changes when two of the input variables change simultaneously (as against One Variable Data Table where only one of the input variable changes). If you want to analyze data when more than 2 variables change, scenario manager is the way to go....

What’s so great about it? It’s quick and easy to learn how to use, and it’ll save you hours and hours of work writing formulas or VBA to do the same sort of things. Let me FILL you in on the details! 😆😂🤣 What Does Flash Fill Do? Flash Fill is a data tool in Microsoft Excel that will allow you to combine, extract or transform data based on a few examples....

To convert these fractional values to decimal hours, just multiply by 24. For example .5 * 24 = 12 hours, .24 * 24 = 6 hours, etc. Durations over 24 hours To calculate and display time durations over 24 hours, you’ll want to use a custom time format like [h]:mm. Otherwise, the duration will “reset” every 24 hours, in the same way that a clock “resets” each day. More on custom number formats here....

What is a formula? A formula in Excel is an expression that returns a specific result. For example: Note: all formulas in Excel must begin with an equals sign (=). Cell references In the examples above, values are “hardcoded”. That means results won’t change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet....

The MODE.MULT function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 254 separate arguments. Examples In the example shown, the formula entered in D5:D9 is: In Excel 365, which supports dynamic arrays, multiple results will spill onto the worksheet automatically....

The PRODUCT function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. All numbers in the arguments provided are multiplied together. Empty cells and text values are ignored. Examples The PRODUCT function returns the product of supplied arguments: PRODUCT multiplies all numeric values together, so the following formulas are equivalent: PRODUCT ignores text values and empty cells....

On Windows, you can sometimes use Ctrl+Alt+Tab to indent and Ctrl+Alt+Shift+Tab to un-indent. However, The application switcher in Windows 7 seems to conflict with these shortcuts. On the Mac, Ctrl+M and Cmd+Shift+Tab also work. Cmd+M should work, but it conflicts with the the Finder minimize window shortcut (see http://apple.stackexchange.com/questions/73886/disable-command-m-to-minimize-window) 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....

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.

While I believe the utility of this feature is high in Word, PowerPoint, and Outlook, it may sometimes be the tool you need in Excel as well. As the name suggests, it converts text into speech and can read out to you while you close your eyes, rest your head on a table and relax. Getting Access to Excel Text to Speech Similar to the Excel Camera tool, even this feature is not readily available in the Quick Access Toolbar or the ribbon....

The TEXTJOIN function takes three required arguments: delimiter, ignore_empty, and text1. Delimiter is the text to use between values that are concatenated together and should be enclosed in double-quotes (""), for example, a space (" “) or a comma with a space (”, “). To use no delimiter, supply an empty string (”"). Ignore_empty is a Boolean (TRUE/FALSE) value that controls whether empty values should be ignored or added to the result....

The TRUNC function takes two arguments: number and num_digits. Number is the numeric value to truncate. The num_digits argument is optional and specifies the place at which number should be truncated. Num_digits defaults to zero (0). Examples By default, TRUNC will return the integer portion of a number: To control the place at which number is truncated, provide a value for num_digits. When num_digits is negative, the TRUNC function will replace the number at a given place with zero:...

Let’s take a look. There are two basic paths to horizontal alignment in Excel. The first is the Alignment group on the home tab of the ribbon, which gives you access to left, right, and center alignment. The second path is the Alignment tab on the Format Cells dialog box. The Format Cells dialog is more complete and gives you access to all the cell alignment options in Excel. As we’ve seen previously, Excel will, by default, align text to the left and numbers and dates to the right....

Radar charts can be used to plot the performance, satisfaction, or other rating data across multiple categories in a single chart. In this worksheet we have rating information for an employee in 6 categories: Technical Skill, Experience, Accomplishments, Personality, Sense of humor, and Work ethic. All categories are rated on the same 5-point scale, where 1 is poor and 5 is excellent. You won’t find radar charts in Recommended Charts....

And when you start working with data in Excel, one of the common tasks you have to do is to adjust the row height in Excel based on your data (or adjust the column width). It’s a really simple thing to do, and in this short Excel tutorial I will show you five ways to change row height in Excel Change the Row Height with Click and Drag (Using the Mouse) The easiest and the most popular method to change row height in Excel is to use the mouse....

What is a Sales Funnel? In any sales process, there are stages. A typical sales stage could look something as shown below: Opportunity Identified –> Validated –> Qualified –> Proposal –> Win/Loss If you think about the numbers, you would realize that this forms a sales funnel. Many opportunities are ‘identified’, but only a part of it is in the ‘Validated’ category, and even lesser ends up as a potential lead....

Working efficiently with Excel also means that you have to work with multiple worksheets within the same workbook. Excel allows you easily add multiple worksheets in the workbook, at the same time you can also easily delete these worksheets. In this Excel tutorial, I will show you how to quickly delete worksheets in Excel. I would cover multiple methods such as using keyboard shortcuts, options in the ribbon, and VBA to delete sheets in Excel....

Most Excel users spend the majority of their time working with the cells in Excel – be it entering data or formulas or editing the already entered formulas/text. In this tutorial, I will cover everything you need to know about editing cells in Excel. While it’s a straightforward process, knowing the right way to edit cells and all the keyboard shortcuts you can use to edit cells in Excel would be quite useful in making you more efficient....

Unlike the MATCH function, which gets the position of an item in a list or a table, INDEX assumes you already know the position, and it gets the value of the item at that position. Let’s take a look. INDEX is a powerful and flexible function that can be used for advanced features like dynamic ranges and two-way lookups. However, in this example, we’re going to use INDEX for the simplest use case possible—retrieving items from a known position....

Rounding a number to the nearest integer or the nearest 0.5 or 5 or 10 multiple is a common task for many people. For example, if you’re a project manager involved in effort estimate, you can’t have 12.7 full-time resources working on a project. You need to round-up this number to 13 (the nearest integer). Similarly, if you’re buying stocks that are sold in a batch of 5, and you can afford a maximum of 123 shares only, you need to round it down to 120 (i....

One such area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates. For example, if you enter 1/2 in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you’re in the US) Similarly, if you enter 30-06-2020, Excel assumes you want to enter a date, and it changes this to a date (as shown below) While this may be what you want in most cases, but what if I don’t want this....