8 cool ways to identify and remove duplicates in Excel

When working with data in Excel, it is very likely you will encounter duplication. How do you deal with duplicates in Excel? Dealing with duplicates in Excel is 2-stage process –first identify if you have duplicates, and secondly remove the duplicate records once you ascertain they don’t need to be in your list. This article discusses 8 cool ways to identify and remove duplicates in Excel. Continue reading “8 cool ways to identify and remove duplicates in Excel”

How to Combine Data from Multiple Worksheets into One Master Worksheet

  • combine Multiple Worksheets Using Excel Macros

One of the common headaches I have come across in the course of my career as a Trainer in Excel Dashboards is the question of how to combine data from multiple worksheets into one master worksheet, so that you can analyze smartly.

Many general users of Excel retire to the time consuming method of copy and pasting, which works, if you have few worksheets. It doesn’t have to be that way! Learn at least 3 ways at your disposal to combine multiple worksheets into a consolidated position. Continue reading “How to Combine Data from Multiple Worksheets into One Master Worksheet”

How to Consolidate Data from Multiple Worksheets in Excel

Do you have data in multiple worksheets that you wish to aggregate into a single sheet? Then this article is primarily written to address just that -how to consolidate data from multiple worksheets to a summary worksheets in Excel. Continue reading “How to Consolidate Data from Multiple Worksheets in Excel”

Using Excel Macros to Combine Multiple Sheets

Using Excel macros to combine multiple sheets is one of the ways to greatly enhance productivity by automating and speeding up the process, especially where the number of worksheets are many. If you have not gone through the various approaches, follow these links:

Continue reading “Using Excel Macros to Combine Multiple Sheets”

Excel Dashboards: Show top/bottom values

If you have used the MAX and MIN functions in Excel, then you know they return a single value representing the highest and the lowest value in a given range.  LARGE and SMALL functions on the other hand allow you to specify whether you want the 2nd, 3rd, 4th, largest/smallest value in the same range. As such, you have to specify the range of values, as well as the ‘Kth’ element, where ‘K’ is any number 1, 2, 3, and so on. The ‘k’ element tells Excel what top/bottom values to return from the specified range. Continue reading “Excel Dashboards: Show top/bottom values”

Using Shapes for Stylish Excel Dashboards

Creating an Excel Dashboard for data visualization is one of the key skills analysts in vast majority of organizations require. The good news is that Microsoft Excel has continued to make improvements that allow the process and available visualizations (charts) easy to generate. For instance, with Excel 2016, you have a Waterfall chart as a standard chart. With Excel 2013 and prior versions, creating a waterfall chart would require some tweaking, but yes, it is achievable (follow the link to learn the 4 steps). I will discuss some key concepts among the undermentioned. You can download a copy of the file used in this example at the bottom of this article.

  1. Custom calculations in Pivot Tables
  2. Adding interaction to dashboards with slicers
  3. Custom number formats and conditional formatting
  4. Using shapes for a prettier excel dashboard
  5. Applications of VLOOKUP + MATCH and named ranges

Continue reading “Using Shapes for Stylish Excel Dashboards”

4 steps: How to Create Waterfall Charts in Excel 2013

Safaricom, the most admired brand in the telecommunication industry in Kenya has seen yet a more profitable year 2015 with total revenue having a YoY growth of +12.9% to 163.4bn, whereas Free Cash Flow (FCF) grew by +21.3% to 27.5bn (Source: Safaricom Financial Results). What fascinated me though is the use of the waterfall chart to visualize both the revenues and the FCF. But have you ever wondered how to create this chart in Excel? This post will give you the 4 steps you need to create a waterfall chart in Excel 2013.

WF2

Continue reading “4 steps: How to Create Waterfall Charts in Excel 2013”