Excel: Tables – Conditional-Formatting, Filters, and Charts

Table features: Conditional-Formatting, Filters, and Charts. In this article, I’ll illustrate powerful Excel features provided with tables such as: Conditional-Formatting, Filters, and Charts. First, I’ll begin with a simple data set. You’ll notice some of the values are truncated, so I’ll click the top-left cell to select all cells, then double-click the dividing line between…

Excel: String Split

Splitting a string with multiple delimiters in Excel. Often when using Excel, especially when viewing CSV file exports from other system, the need arises to transform the existing values into separate values, each residing within their own cell. Below is a small recordset of Sales people records with their Sales Region (one digit numeric code)…

Excel: Statistics

Using statistic formulas to deliver key metrics. Beginning with a simple set of test sales data, I employed some simple statistic Excel formulas to display metrics regarding the data. In addition to using Conditional Formatting to show revenue values in both the high (green) and low (red) ranges, I also show for all the values…

Excel: Solver

Automate scenario analysis of constraints to achieve goals. Product management leverages numerous factors to achieve optimal success with product outcomes such as constraints (limitations upon resources) and decisions decisions on those constraints. Optimization allows team members to influence certain decisions about constraints, delivering differing results according to the team strategy. In this article, I will…

Excel: Power Query – Transformations

Implementing Business Rules through Transformations In this article, I’ll continue from the article Excel Power Query – Introduction and illustrate some transformations available in the Query Editor. I’ll begin with the data loaded into the Query Editor. Remove Columns Since I don’t need the field Ship Mode, I’ll use “Remove Columns” to remove this column.…

Excel: Power Query: Introduction

Leveraging Power Query to harness your data. The Power Query Add-On provides power capabilities to connect to multiple data sources, import data, query and transform data. Power Query allows you to load millions of rows into Excel’s Data Model which keeps the data in memory instead of numerous worksheets. This enormous data storage allows analysis…

Excel: Optimization

Automate scenario analysis of constraints to achieve goals. Product management leverages numerous factors to achieve optimal success with product outcomes such as constraints (limitations upon resources) and decisions decisions on those constraints. Optimization allows team members to influence certain decisions about constraints, delivering differing results according to the team strategy. In this article, I will…