May 1, 2019 - Page 5 of 5 - Bonbon's Guide To Data & Tech

[FREE Excel Course] Lesson 10 – Logical Formulas in Excel

Posted on May 8, 2019 by Annie D

In this video of this Free Online Excel Training, you will learn about the following logical formulas:

— AND function
— OR function
— NOT function
— TRUE function
— FALSE function
— IF function
— IFERROR function
— IS function

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

[FREE Excel Course] Lesson 9 – Excel Formula Basics

Posted on May 8, 2019 by Annie D

In this video of this Free Excel Training, you will learn how to get started with Excel formulas.

This video covers the following topics:

— Constituents of Excel formula
— Operators in formulas
— Entering a formula in Excel
— Editing a formula
— Absolute/relative cell references
— Copying/pasting formulas in Excel, and
— Formula auditing and debugging.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

MSPTDA 22: DAX Data Modeling to Create Date & Time Dimension Tables, Server Downtime Visualizations

Posted on May 7, 2019 by Annie D

All 7 files and folders with text files used in video are available in this zipped folder: http://bit.ly/2VQiE2k

Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #22: http://bit.ly/2VOExiE

In this video learn about how to import a CSV Text file with server computer downtime, create the Data and Time Dimension Tables using DAX Table formulas and DAX Calculated Columns, Create Relationships and Measures to then build a Dashboard with Visualization for server downtime by year, month, day and hour. Learn about Custom Visualizations that you can download, and see the Word Cloud Visualization.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (00:43) Import Large CSV File
3. (00:58) Look at Final Power BI Visualization
4. (01:36) Import Large CSV File
5. (02:00) Data Modeling on Fact Table with Power Query, including extracting date and time values from a single date-time column and filtering rows base on an Upper and Lower Date.
6. (04:20) Create Date Dimension Table with DAX Formulas. See the CALENDAR DAX Function and more. See how to use FORMAT DAX Function to get a Day Data Attribute with Day Name. See how to Sort By Column to get Data Attribute Columns to sort correctly in visualizations.
7. (08:20) Create Time Dimension Table using DAX Formulas. Learn about the GENERATESERIES DAX Function and more.
8. (09:16) Create DAX Calculated Column for Serial Time.
9. (10:00) Create Time Category using FORMAT Function. See two methods. Sort By Column to get Times to sort correctly in visualization.
10. (11:50) Hide Columns
11. (12:37) Create Measure for Total Server Downtime
12. (13:05) Create Relationships
13. (14:20) Create Line Chart with Year, Month, Day and Hour is the horizontal axis
14. (14:45) Learn about Drill Down in Power BI
15. (16:05) Create Matrix for Reason Server Computer is down
16. (16:30) Create Measure using SELECTEDVALUE DAX Function to use in a Card to show what Machine is being analyzed.
17. (17:20) Word Cloud Visualizations
18. (17:42) Download Custom Visualization in Power BI
19. (19:28) Summary

View on YouTube

[FREE EXCEL COURSE] Lesson 8 – Number Formatting in Excel

Posted on May 6, 2019 by Annie D

In this video of this Excel course, you will learn all about number formatting in Excel.

This video covers the following topics:

— How to access number formatting options
— Using number formatting options using a dialog box
— Using custom number formatting

Remember that formatting a number in Excel only changes how it’s displayed, but the underlying value remains the same.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE EXCEL COURSE] Lesson 7 – Auto-fill, Custom Lists, and Flash Fill

Posted on May 6, 2019 by Annie D

In this video of this free Excel course, you will learn about tools that can help you when working with data in Excel.

In this lesson, I cover the following topics:

— AUTOFILL: This allows you to quickly fill data in contiguous cells. For example, you can have ‘Jan’ and ‘Feb’ in two adjacent cells and then you can use Autofill to get the other month names

— CUSTOM LISTS: Custom lists allows you to create your own lists that you can use in Autofill. For example, if you have 20 names, you can create a custom list and then use Autofill to get all the names by just dragging the mouse

— FLASH FILL: Flash fill is a new feature in Excel 2013 and allows you to identify a pattern in data and perform data slice and dice based on the pattern.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE EXCEL COURSE] Lesson 6 – Excel Tables Overview

Posted on May 6, 2019 by Annie D

In this video, you’ll learn how to use Excel tables.

Excel tables are amazing, especially if you plan to work with a lot of data/dashboards/models.

And now that new tools (such as Power Query and Power Pivot) are available, it has made Excel tables even more powerful.

In this video, I cover the following topics:

— Creating an Excel Table
— Excel Table features
— Structured references
— Table slicers
— Note that table slicers became available only from Excel 2013 -version onward.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE EXCEL COURSE] Lesson 5 – Managing Worksheets in Excel

Posted on May 6, 2019 by Annie D

In this video, you’ll learn how to manage worksheets in Excel.

This video will teach you a lot about working with worksheets.

For example, if you’re working with large datasets, you can use the functionalities such as splitting the worksheet or freeze panes to be more efficient.

It covers the following topics:

— Navigating through worksheets in Excel
— Changing worksheet name and color
— Adding/deleting worksheets
— Hiding/Un-hiding worksheets
— Moving/copying worksheets
— Comparing worksheets side by side
— Splitting worksheet into panes
— Freeze panes
— Saving and opening workbooks

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also get this training and more resources here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE EXCEL COURSE] Lesson 4 – Working with Cells and Ranges

Posted on May 6, 2019 by Annie D

In this video, you’ll learn how to work with cells and ranges in Excel.

Since a worksheet in Excel is made up for cells, if you want to become proficient in Excel, you need to know how to best work with cells and ranges.

And this video will teach you exactly that.

It covers the following topics:

— Cell address nomenclature
— Selecting cell/ranges in Excel
— Copying and moving cells/ranges in Excel
— Using paste special
— Selecting special cells
— Using find and replace to select cells
— Adding comments and
— Using quick analysis

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also get this training and more resources here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE EXCEL COURSE] Lesson 3 – Data Formatting in Excel

Posted on May 6, 2019 by Annie D

This video covers the basics of data formatting in Excel.

You can format data in Excel to make it look better and to highlight some data points.

For example, if you have data-set and you want to highlight the negative numbers, you can simply highlight it in red color to it.

Here are some of the common formatting I use to make my data look better and more readable

— Applying borders to all cells
— Making headers bold
— Giving a background cell color to headers.
— Center align the headers

You can find these options in the ‘Home’ tab in the Font category.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also get this training and more resources here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE Excel Course] Lesson 2 – Data Entry, Editing, and Number Formatting

Posted on May 5, 2019 by Annie D

This video covers the basics of Data Entry, editing as well as number formatting.

Once you know are comfortable with the layout of Excel, the next step is to learn how to enter and edit data in Excel.

You can enter three types of data in Excel – numeric, text, and formulas.

To enter data in any cell, just select the cell and start typing. Once done, press the enter key to move to the cell below. In case you are entering dates, enter it in a format that Excel recognizes as a date (such as 01-01-2020 or 01 Jan 2020 or 01/01/2020).

This video will show you how to enter data in Excel and edit already added data (such as deleting data, modifying existing data and replacing data).

Apart from this, it also covers the auto-fill in Excel as well as number formatting.

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more information and the download files here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

[FREE Excel Course] Lesson 1 – Getting Started with Excel

Posted on May 5, 2019 by Annie D

This video covers the basics of Excel and is meant for people who beginners in Excel and are just starting with Excel.

In this video, I cover the following topics:
— Structure of the Excel working area,
— How to navigate in Excel,
— Ribbons and tabs,
— Quick Access Toolbar (QAT)
— Dialog Box & Task Panes,
— Customizing ribbons and QAT,
— What is an active cell
— How to use ALT shortcuts

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also get this training and more resources here: http://bit.ly/2snelIX

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

-~-~~-~~~-~~-~-

View on YouTube

MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size

Posted on May 4, 2019 by Annie D

All 4 files used in video and homework are available in this zipped folder: http://bit.ly/2JnYkyT
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #21: http://bit.ly/2JfGAW1

In this video learn key strategies and Power Query Transformation Methods for reducing the size of your Data Model, or Columnar Database or File size. This is an important part of Data Analysis and Business Intelligence when you have Big Data because it can help your solution to run more efficiently by creating smaller Data Model behind the scenes Columnar Databases that must be loaded into RAM. Apply concepts of how the Columnar Database stores data when deciding what tables, columns and transformations should be part of the final Data Model.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (01:11) Download HUGE CSV File.
3. (01:34) Talk about How Columnar Database works and how we can reduce size.
4. (03:11) Rules for Efficient Data Modeling
5. (06:07) Get Data From Government Web Site: http://bit.ly/2IYfRht
6. (07:18) Import CSV Text File
7. (07:46) Load Entire CSV File and look at Model Size.
8. (09:20) Filter Rows and look at Model Size.
9. (10:56) Remove Columns and look at Model Size.
10. (11:40) Reduce Cardinality of Columns with 1) Add Column for Time
11. (13:59) Reduce Cardinality of Columns with 2) Transform Columns and get Date Only
12. (15:21) Edit Time Column to Create only Hour and reduce size further.
13. (16:39) Edit Query can lead to trouble in subsequent steps. See how to fix the problem.
14. (17:28) Add Date Table
15. (18:28) Create Relationship
16. (18:44) Create Measure to Count Rows for Call Type. Use COUNTROWS DAX Function.
17. (19:16) Create Dashboard
18. (19:16) Create Pivot Table
19. (20:17) Filter to show Top 20, 911 Call Types
20. (20:45) Show Values As in Data Model PivotTable
21. (22:07) Sort Measure Biggest to Smallest
22. (22:15) Add Slicers
23. (23:05) Scrape Data form Web Site
24. (24:18) Summary

View on YouTube

Bonbon's Guide To Data & Tech

Practical Solutions for Excel, Power BI, SQL, Access, Python, Data Science, Automation & Other Tech

Month: May 2019

[FREE Excel Course] Lesson 10 – Logical Formulas in Excel

[FREE Excel Course] Lesson 9 – Excel Formula Basics

MSPTDA 22: DAX Data Modeling to Create Date & Time Dimension Tables, Server Downtime Visualizations

[FREE EXCEL COURSE] Lesson 8 – Number Formatting in Excel

[FREE EXCEL COURSE] Lesson 7 – Auto-fill, Custom Lists, and Flash Fill

[FREE EXCEL COURSE] Lesson 6 – Excel Tables Overview

[FREE EXCEL COURSE] Lesson 5 – Managing Worksheets in Excel

[FREE EXCEL COURSE] Lesson 4 – Working with Cells and Ranges

[FREE EXCEL COURSE] Lesson 3 – Data Formatting in Excel

[FREE Excel Course] Lesson 2 – Data Entry, Editing, and Number Formatting

[FREE Excel Course] Lesson 1 – Getting Started with Excel

MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: