Consolidating data into one Excel worksheet. This post describes how to merge data in an Excel workbook containing worksheets with data where each worksheet has differing number of columns and rows. I will use a spreadsheet which contains three tabs/worksheets with the following columns. Sheet1 will be the first source worksheet used to import data.…
Please see my other Business Intelligence articles. Leveraging Excel to deliver analytics. CHARTS Chart: Area Chart: Bar Chart: Bar – Clustered Chart: Bar – Stacked Chart: Bar – Vertical Chart: Bubble Chart: Histogram Chart: Histogram – Frequency Polygon Chart: Histogram – Relative Frequency Chart: Line Chart: Ogive Chart: Pie Chart: Stem & Leaf Plots…
Creating a list lookup. Many Excel spreadsheets contain data spread across many different worksheets and in collaboratively work environments, worksheets are shared among coworkers. The result of this data separation is that sometimes a scenarios arises when critical data is needed but unavailable by the current spreadsheet. To overcome this limitation, I will use the…
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…
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)…
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…
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…
Have you printed a worksheet with multiple pages, but wished the header would appear on each page? 1) Click Print Titles. 2) On the Sheet ta, click once in Rows to repeat at top:, then click the far-left of the row to repeat (A1), click OK, the print. Now my header Header 1, Header 2,……
Providing users with helpful header information When collaborating on a spreadsheet, team members appreciate helpful information displayed at the top of each page. This can easily be achieved by utilizing the page’s header area and a few properties as this article will illustrate. When you begin in Excel, you’ll notice the spreadsheet appears to have…
Implementing Business Rules through Advanced Transformations In this article, I’ll continue from the article [Power Query – Transformations] and illustrate some advanced transformations available in the Query Editor. I’ll begin with the same data from the previous article. Split Column By Delimiter First, I wish to parse “Order ID” values so I end up with…
TechMbaBi 