In the previous article we discussed the SUBTOTAL function https://windsongtraining.ca/an-example-of-using-the-subtotal-function-in-excel/ https://windsongtraining.ca/an-example-of-us…unction-in-excel/. In this post we will look at the SUBTOTAL command found on the Data tab in the Outline group. Each Excel function provides a specific calculation. We use any of the over 500 functions that Excel provides by typing an = sign in a cell [...]
Microsoft Excel has a powerful and relatively new tool to add to the arsenal of data analysis tools that we are already accustomed to using, this is the Excel Data Model. Working together with Power Query and Power Pivot, the Data Model enables Excel users to work with much larger data sets than we could [...]
Microsoft Excel is a spreadsheet program with an interface formed from a set of cells arranged in rows and columns. The columns are labelled with letters along the top of the work area, and the rows are numbered down the left side of the work area. Every cell has a unique address formed by combining [...]
Excel's What-If Analysis tools allow us to change the values of inputs to a calculation and explore different possible results. In the following article we will consider a single, simple calculation and discuss how each of the three What-If Analysis tools: Goal Seek, Scenario Manager and Data Tables, can be applied. The commands for What-if [...]
What comes to your mind when you think about Excel? Most of us think about building tables for data sets, using formulas and tools to analyze data, displaying the data by using charts, and maybe some are eager to do analysis with PivotTables. We have access to multiple tools on the Excel tabs to make [...]
Excel has 4 functions that allow us to find a value in a column or row, and return a value from a corresponding column or row. The lookup functions can be very useful and are easy to understand when they provide an exact match for a search item. In addition to exact matches, the functions [...]
Functions in Excel can work with multiple cells by providing ranges as input parameters. The Excel documentation will refer to these ranges as either "vector" or "array" ranges. A range is specified by a beginning cell address, a colon, followed by the final cell address. A valid range in Excel looks like: D4:D10, which includes [...]
“Politicians use statistics in the same way that a drunk uses lamp-posts for support rather than illumination” -A. Lang Statistics is a very complex subject and because of its complexity statistics and its terms are frequently misunderstood and misused. I came across an excellent article on The datapine Blog about how statistics can be misleading. [...]
I was reading about one of the new functions that Microsoft has added to Excel, XLOOKUP(), and I started wondering where the different LOOKUP() functions came from. This little rabbit hole turned out to be quite revealing in terms of the way that Excel looks and works - the search took me to some history [...]
In this article we are going to look at Excel’s Goal Seek command. This command is actually quite easy to use, and it is very popular because of its ability to look backwards from a given result. A common example used to illustrate this command is finding the amount a person can borrow, at a [...]
