Dynamic Arrays | FILTER | Retrieve Only Specified Columns

/in /by

The new FILTER function, as part of the new Dynamic Arrays in Excel, is fantastic. However, it is set to filter and retrieve data from contiguous datasets. This is a problem when, for example, a person needs 5 columns from a data-dump of 30 columns.

This video shows how to use the CHOOSE function to both, FILTER and retrieve specified columns–only those columns that you want.

Download the workbook: https://datascopic.net/FNA

 #DynamicArrays
#FILTER
#CHOOSE

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

Data Literacy, Power Query, Understanding Data and Reports

/in /by

This video is about using Excel in the real world. Here, I was faced with a situation where a new way of paying commissions was created, but a new report for me to use wasn’t also created. Instead, an existing report was tweaked and it seemed good enough until someone asked a question and requested more transparency. OOPS!

And this is where data literacy comes in.

To figure out the problem, it was necessary to use Excel in a supporting role to dig into questions like:
– What was this report really meant for?
– What are the definitions in the report?
– What are the triggers in the report?
– Do the calculations accurately reflect what’s intended and what’s in the compensation plan?
– What are weaknesses in the report?
– Where can I turn for transparency?

In situations like these, it’s not ok to blindly rely on a report. When there are questions, dig in.

This video also considers how I might have handled this problem if I had Power Query at the time. You’ll see:
– Left outer join
– Power Query’s Replace Values
– Remove columns
– Filter
– Reference Query

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Investigating Data with Bar Graphs and Pivot Tables

/in /by

There are a couple of reasons for this video.
1. Often, to be good with data, the technical Excel tips & tricks aren’t necessary. Sometimes we have to think, probe and strategize.
2. This follows on my previous video that showed how graphing the data unlocked a mystery. Here again, is a true situation where it helped to make a pivot table and trow the data into a bar chart.

The video also uses Tables, and the WEEKNUM function to get to the bottom of the mystery.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
#WEEKNUM
#PivotTable
#ColumnChart

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel SLOPE Function and Scatter Plot for a Pricing Formula

/in /by

This video takes us back to my very first paid project in Excel. A photographer had a problem calculating the price of a finished and framed photo. The main part of the problem was the mystery around the price of the frame.

We gathered receipts and I figured, “what the heck, let’s plot the known data on a grid.”
The Excel scatter plot showed a straight line. That made it easy to figure out the frame shop’s pricing formula.

The equation of a straight line: y=mx+b

 
y = final price
m = slope * length * width + b
EASY!!!

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

IDEAS in Excel: Be wary of these weaknesses

/in /by

Excel has a feature called Ideas. It offers to provide information about the user’s dataset, and accepts natural language questions. This video shows areas where you need to watch out or you can get into deep trouble.

I’m skeptical of AI/ML (artificial intelligence and machine learning). Why? Because the hype and marketing around this technology talks about the promises and what’s possible. But this video shows several examples where Ideas (using AI/ML) makes goofs.

Ideas–like any other AI/ML application–makes guesses. Some guesses are much better than others. Some guesses are way off the mark and if we have to be extra careful about results that we didn’t generate ourselves.

Yes, AI/ML can be helpful. But it puts more responsibility on the user to understand and verify everything around their data.

#ExcelIdeas
#Ideas
#ArtificialIntelligence

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Import from PDF (2 Examples)

/in /by

One thing has been torture for those of us who work with data: breaking data out of PDF files!

Just recently, Power Query got a new feature: import from PDF. In this video I show 2 examples of importing a PDF into Power Query and also show the old methods of a plain old copy-paste and “open with Microsoft Word.”

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Managing Accuracy and Errant Entries with SUMIFS and Conditional Formatting

/in /by

This video pulls together a number of Excel features in order to get an accurate inventory number.

This is based on a real life situation where an organizations data was messy because they weren’t adding numbers properly or managing errant entries.

This video shows how to use SUMIFS, a dropdown list and Excel’s Conditional Formatting to ensure that inputs and outputs are accurate.

#SUMIFS
#ExcelErrors
#ConditionalFormatting

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel COUNTIFS function with OR Criteria

/in /by

#COUNTIFS
#CountMultipleCriteria
#COUNTIFSfunction

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Split Multiple Columns into Rows All At Once

/in /by

It’s easy to split columns in Power Query and split into rows. But here’s a situation where there’s data in 3 columns that will fly apart and get unmatched if we split one column without splitting the other 2 at the same time.

One solution would be to split the source data into 3 pieces, split the columns into rows, add index columns, then use outer joins to piece the data back together.

We’re not going to do that today!We’re going to use Power Query’s lists, and 2 functions:
Text.Split
Table.FromColumns

These are going to make for a smooth solution!

#SplitMultipleColumns
#PowerQuery
#Text.Split

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2