Google Sheets - Covert Columns or Rows into Comma Separated Values

Been in a situation where you had to convert values in columns or rows into comma separated values (csv)? Maybe you got a column of value and you needed to query it in another system, BUT that system needed the input values separated by commas (or semicolons). Using Google sheets, there are actually a couple of ways you can do this and I'll show three examples with three different functions to make it happen.

Animate a PPT with the Zoom Feature

Been in a situation where you had to convert values in columns or rows into comma separated values (csv)? Maybe you got a column of value and you needed to query it in another system, BUT that system needed the input values separated by commas (or semicolons). Using Google sheets, there are actually a couple of ways you can do this and I'll show three examples with three different functions to make it happen.

Group Dates in a Pivot Table

Grouping dates in a pivot table is one of those nice slice and dice features. If you get dates in a MM/DD/YYYY or DD/MM/YYYY format and want to see your data aggregated or rolled up into months, quarters, years or any other interval you need to group the dates. What is nice is you can expand and collapse the months (or whatever grouping or ungrouping you chose) to simplify your views. The other nice thing is that you can put the pivot table data into a chart and use the expand and collapse capabilities from the table to affect the chart or use the expand/collapse buttons on the chart itself. This chart can also be put on a separate sheet so you don't have to see the table. 

A note that is discussed in the video is what happens when you can't group and it may have something to do with the date format (Hint: It's not working because of text). There's another note on how this date grouping can be automatically done or undone.

Animate a Social Media Phone Feed in PowerPoint

PowerPoint is great for animations and if you wanted to simulate a mobile phone screen as if someone was to browse through their social media feeds, this can be done in most versions of PowerPoint quite easily. In the latest versions of PPT (Office or Microsoft 365 subscription or 2019), you can use the Morph feature to do this. If you don't have those PPT versions, this can be done via the animation command. This video will show both examples of how to animate scrolling through a feed on a mobile phone image.

Turn a Picture into Puzzle Pieces in PowerPoint

If you want to create a puzzle piece image out of an existing image, you'd think it was something that could only be done in Photoshop or pay someone to do. With most versions of PowerPoint, this can be done with just a few steps. You can event animate the pieces with the morph command (but this would take Microsoft 365 subscription or PPT 2019). Later on in this video I'll show the animation effect using that morph feature. If you don't have the current versions of PPT, you can do this with the animations command, but it'll just take more work (that won't be covered in this video, but subscribe and check out my other animations vid on https://www.youtube.com/doughexcel/?s...). As a bonus near the end of the vid, making an image out of other shapes will be covered so check it out.

Perform Quick Pareto Analysis in Excel

A Pareto analysis tries to find out what variable are responsible for the bulk of the outcomes. It's referred to as a Pareto Principle or the 80/20 rule. The concept can be expressed with the saying that 80 percent of the wealth is owned or created by 20 percent of the population. Though in real life data may not fit tightly in the 20 percent, the idea of Pareto is that a small number of variables have a disproportionate ownership or cause of an outcome. This video show how to quickly find out from your data what variables are responsible for the "80%" in your data using Excel.

Power Query - Importing Table in PDF to Excel

Importing a table from a PDF document into Excel can be a difficult process. You might have been doing a copy and paste and editing or deleting our strange characters, spaces or tabs. Whether it was a small table or large one, it's a time intensive process. If you've got the Microsoft 365 subscription (formerly Office 365 or O365), you might have the latest features from Power Query to help make this an easier process. If you don't have Power Query, it still can be done but with another Microsoft application - Word (see that video here at https://youtu.be/COUOvhbQ4CQ)

Chord Diagram in Power BI

A Chord Diagram or Network Radial Diagrams as a type of chart that is popular in data viz programs. You can't find it in Excel and not natively in Power BI. However it is a data visual that you can import from the Power BI marketplace (and it's from Microsoft). It's a visually appealing chart and quite popular to data viz practitioners. You might think that making this is hard, but with the chord data visualization import from the marketplace, it's really easy. And you don't need fancy programs like R or Python.

Animate a Bubble Chart in Power BI

Animated Bubble Chart probably got their start from Hans Rosling's TED talk demo using their Gapminder tool. This data visualization has come a long way with quite a few tools that enable this animation. Power BI is one of those tools that have this capability. It is part of the scatter chart visualization using the play field. However there is also another data visualization tool you can download from the Power BI Visuals marketplace called Play Axis that gives you some more options. This video will cover creating an animated bubble chart using both.

Excel Combination Chart of Sales vs Target

If you need to chart some kinds of data to a goal like sales to a target, Excel makes this easy with the combo chart of a column chart along with a line chart (initially). The columns can be the sales and the line series will represent the target. Most times this is enough when you have a target that doesn't change much. However if there are change to the target having the line vary may not looks visually appealing. Another example in this videos that is also a combo chart is actually using the combination of two column charts but have them on different y axis.

Convert Numbers to Words in Excel

Convert numbers to words seems lot a often sought out request for Excel. One day it may be a function or even command that can be entered or clicked on the ribbon. However it's not, but there is a workaround solution that Microsoft has provided in the form of VBA. They actually posted a knowledge article (along with the code) on a function call SPELLNUMBER. Just do a web search for this text to find the Microsoft page to get the code and instructions on how to apply it. One caveat is that there will be some errors to troubleshoot through, but it wasn't so bad.

Risk Assessment Chart in Excel

In Excel, creating a risk assessment chart (aka impact / probability chart) is fairly easy. All you need is the risk assessment matrix (with a not so large scale) and about fifteen minutes of time. Once finished you can put this into your favorite presentation software (PowerPoint?) and even make this into a template for the future.

Case Insensitive Lookup with Power Query

If you are trying to do a case insensitive keyword lookup for a table of records, you could just use the filtering capabilities in the Table feature. But if you want to make it more easy for users, maybe having them enter a word in a cell and than refreshing a table would be better. One solution would be to use Power Query!

Use Power Query to Append Multiple Tables

Appending or combining tables that have common headers in not hard to to if you only have a few. But if you have a couple or combine common tables on a recurring basis, it would be nice to automate this. Power query give you this option to make it easy to do. Whether you're joining one to three tables or if you do this on a monthly, weekly or daily basis, this can be done quickly once you have it set up.

Dynamic Combo Chart in Excel

A combination chart is a chart type that has more than one type of chart in a single view. Most times it's a combination of a column and line chart where it would plot something like volume and revenue together. The volume data can be a column chart and the revenue data can be the line chart. Excel has provided this combo chart capability for quite a while but in later version it became very easy as a chart type selection (which this video will cover). However there not really an option to give interaction like selecting to view the column chart alone and then both chart type together with a click of the button. This video will cover a quick hack on how to create a dynamic or interactive combo chart using slicers.

Power BI Animated Bar Chart

An animated chart in Power BI is nothing new since you could create an animated bubble chart. But the animation of a bar chart has been something that has been circulated around the Internet in some form or another outside of Microsoft products. You can experience it from Hans Rosling's Gapminder application to the Charticulator tool. This example will use covid-19 data from the New York Times dataset that can be found at github. Though not as entertaining as Trevor Noah's skit on using Excel to Flatten the curve (just search for "Microsoft Excel: Coronavirus Edition"), this hopefully is another edu-taining way to see data.

Changing Time Zones in Power Query

When you get date and time values from another systems, sometimes it is in a time zone that is different from yours. For example you might get a field that has a date/time/timzone in UTC or GMT format, but you need to doanalysiss in Western time. How do you convert the timezone? You could actually do the calculations and offset it by adding or subtracting hours to the UTC value. Switching time zones gets more complicated if you have to deal with daylight saving time. You'd have to find out which zonehour had daylight saving applied and which did not. How you handled the time zones begins by setting or configuring an initial time zone in Power Query for the date/time value.

Unpivot Data with Null or Error Values in Power Query

If your familiar with Pivot Tables, it's basically taking a proper table and summarizing the data for further analysis. However what if you got a pivoted table (aka crosstab or contingency table) already and wanted to do the reverse? In Power Query, there's the option to unpivot (uncrosstab or sort of a transpose) a pivot table view. However it does certain things to the data if it has errors or null values and maybe you don't want Power Query to remove the empty cells or show the error cells. This video will cover both scenarios and the steps to address them.

Add Multiple Series of Data to X Y Scatter Chart

If you're creating a scatter plot it may begin with one series of x,y data. But later on you may want to put a second set of data or a second line into your chart. You may have 2 variables, 3 variables or multiple variables! Basically this are multiple groups or multiple sets of data and you'd want to plot two or more things at once. This vid will show you how to quickly add multiple scatter plots into your graph chart and even cover labeling your series of data. Well it covers more of how to label your series of data via the legend and even though Excel can give you two or more colors to separate out those series, you may want to use markers to help differentiate it more.

Extract Hyperlinks from Sentences in a Column

If you were to pull out hyperlinks (or URLs) from a table, it would be fairly easy if it were in the beginning or end of the contents in a cell by using some combinations of the LEFT, MID, SEARCH or FIND functions. But if the URL in the text cells were all over the place and part of a paragraph in a cell, it might be more challenging or could involved using VBA. Common cases would be to pull out URLs with http, https or www and this video will show how to extract those type of URLs using Power Query.