Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups

See how to use Excel Formulas to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons) and then extract the customer names in each group. The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
5) Goal 2: List Customers who visited a given set of stores.
Download File: http://ift.tt/2tvSeEE
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics in Video:
1. (00:12) Introduction to Problem
2. (03:33) Count number of coupons each customer redeemed for each store. See the COUNTIFS Function.
3. (07:03) Logical formulas for determining which customers fall into each of the eight coupon groups. Learn about AND Logical Tests, OR Logical Tests, and a NONE Logical Test (None are TRUE, or All are FALSE). See the Excel Spreadsheet Logical functions: AND, OR, NOT.
4. (17:23) Count Customers in each of the eight coupon groups using COUNTIFS Function.
5. (18:02) Formula to Extract Customer Names for each of the eight coupon groups. Learn about Array Formulas to looks an item and Return Multiple Items. Learn about the Excel Spreadsheet functions: ROW, AGGREGATE, ROWS, INDEX and IF.
6. (29:25) Conclusion

View on YouTube

The Magic of Excel: Formulas, PivotTables, Charts, Flash Fill, Sort, Filtering, Functions & more!!

This video in an introduction to Excel and the Magic it can do to make your life and job easier, more efficient and more fun! It is an introduction to many of the amazing features in Excel that can help us in our job, life and in school. This video is part of Highlines effort to show students that going to college can be fun and amazing for empowering student to do what they want in life!

Download File Here:
Start File: http://ift.tt/2sexb9S
Finished File: http://ift.tt/2sYhnW6 File.xlsx
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

Topics in this video:
1. (00:36) What is Excel?
2. (03:11) What does Excel do? 1) Calculations, 2) Storing & Formatting Data, 3) Data Analysis
3. (10:28) Formatting to make our Data Look Good. Build a Template that you can use over and over.
4. (12:35) See how to create a List of Days of the Week Quickly using the “Angry Rabbit”.
5. (16:14) Formulas & Cell References to make our Job Easier! Learn about the Excel Functions: SUM, AVERAGE, MAX and MIN.
6. (21:10) Keyboard Shortcuts are Amazing. Learn how to highlight 5000 rows of data quickly.
7. (26:52) Payroll Tax Calculations Hard? Not With Excel : )
8. (29:59) Relative Cell References in Formulas
9. (31:01) Absolute Cell References in Formulas
10. (36:10) Excel Table Feature makes everything Dynamic!!! Learn about how to Sort, Filter and create Dynamic Ranges (Update Automatically) using the Excel Table Features.
11. (44:31) Chart Your Results Dynamically!! Learn how to create a Column Chart for visualizing the difference across Categories.
12. (48:53) Boss Ask you to add All Sales for Each Employee!?!? Easy with a PivotTable! See how to create a Sales Report with a PivotTable, including how to Format and Pivot the PivotTable.
13. (56:20) Flash Fill to transform your Data in Seconds!
14. (60:05) Summary of all topics.

View on YouTube

10 Acrylic Painting Tips for Beginners

Hello there! I have been dabbling with paint for a while but this semester I took my first painting class. It is so crazy because I have taken a bunch of drawing classes but never a class that focused on how to paint.

In this video I’m sharing the tips I have learned so far from working with acrylics! These are pretty basic tips but these are things that have been really helping me out and I think they will help you if you are a beginner too.

Here are my Tips:

1. Buy Basic Canvases:
I like using canvas boards because it encourages me to go for and practice. I don’t feel like I have to make everything perfect because I didn’t spend a bunch of money on supplies. Also they are WAYYYYY easier to store in your home.

2. Create more:
It’s better to explore and experiment when you are first learning how to work with paint then trying to make everything look perfect right away. As writers like to say: write for the trash can! You are going to learn more and improve quicker by being less timid and more open. So go paint some train wrecks and you will eventually paint a masterpiece.

3. Try painting without Drawing First:
I feel like you might come after me with torches and pitchforks for saying this but… maybe give it a try? I know how you feel I am SO much more use to drawing then painting but just learning how to work with my brushstrokes at this point has helped me prevent making my work look too stiff.

4. Use a Bigger brush:
I know you little pencil artists out there like me just want to use a fine tip to get in all those little details… But if you start your paintings this way, it is a PAAAIIIN. Also working with a bigger brush to start helps you create more movement and brushstrokes. (also saves you from ripping out hair)

5. Use the Rule of thirds:
Now you don’t always have to do this but it does make the composition more interesting to look at. If my explanation didn’t make sense, here is another example: http://ift.tt/1hTNRXx

6. Buy Small Paint Tubes:
Now I know you are going to think I’ve lost my mind on this one but these little mini tubes have gotten me through SEVERAL assignments on my class. I did not think this was possible AT ALL. Paint adds up quickly, use these little tubes to test out what brand works best for you.

7. Use a gel medium:
I LOVE this stuff. I have had it in my art collection for a WHILE but I never really used it. THIS STUFF IS AMAZING! It makes everything glide much easier and it keeps the paint wet. For only $2 it is a bargain. I wouldn’t recommend using this stuff for the first layer, wait till your 3rd layer. It makes the paint less opaque.

8. Use a Heavy Matte Medium:
Yes girl! If you have always wanted to add some texture to your paint, here you go. You mix this stuff with your paint and it makes it stand out on your canvas.

9. Mix compliments:
I use to HATE. I mean HAAAATTTE complimentary colors… so much. They just looked so repulsive and ugly to me. But when you mix them together, they neutralize eachother put and create these useful neutrals. These neutrals are great for so many things: shadows, skin. Basically anything you are painting from life.

10. Be bold and experiment with mixing colors:
When you are first starting mixing colors is super intimidating. However, you got to give it a shot. Mix colors you would NEVER think of mixing together. You might surprise yourself.

Those are the tips I have for you guys! I hope you enjoyed this video. If you did you can show your support by commenting and liking. I always appreciate hearing from you.

Happy Painting!
xo Heather
via YouTube https://youtu.be/ZRwFbOMDcw4

Sync Slicers from Different Data Sets

Learn Excel – Sync Slicers from Different Data Sets

How can you have a slicer drive two pivot tables?
If both pivot tables came from the same data set: Select Slicer, Report Connections, Choose Other Pivot Tables
But if the pivot tables came from different data sets:
Use Save As to change the workbook extension to XLSM instead of XLSX
Use Alt+T M S and change macro security to second setting.
Alt+F11 to get to VBA
Ctrl+R to display the project explorer
Find the worksheet that contains your first pivot table and slicer
Insert the code for Worksheet_Update
Hide the second slicer away so it keeps existing but no one can ever choose from that slicer
via YouTube https://youtu.be/1fOeIS-UpNU

Excel Formula to Create Cross Tabulated OR & AND Logical Test Sales Total

Excel Formula to Create Cross Tabulated OR & AND Logical Test Sales Total

Download File:
Start File: http://ift.tt/2rKDr8N
Finished File: http://ift.tt/2sjWh3G
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See how to use SUMIFS and SUMPRODUCT Functions to create a Cross Tabulated Table to add Sales with Sales Channel Criteria as the Row Header Criteria and Product Group OR Criteria as the Column Header Criteria.
Related Videos:
Excel Magic Trick 1439: DAX Formula to Create Cross Tabulated OR & AND Logical Test Sales Total
Excel Magic Trick 1438: Excel Formula to Create Cross Tabulated OR & AND Logical Test Sales Total
via YouTube https://youtu.be/KI0JQQuBJZc

VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

Download Start File: http://ift.tt/2s2NKpb
Download Finished File: http://ift.tt/2sNrjS5
Entire Page with All Files for All Videos: http://ift.tt/1JhAg9o
Then scroll down to this section: “VLOOKUP “Shark” Week Downloadable Workbooks”
Look for the file named: VLOOKUP-SHARK-WEEK-DownloadableWorkbook01-Start.xlsx

Excel VLOOKUP is one of the most important functions because everyone needs to look stuff up. Not only that, but if you want to get a job, you must know VLOOKUP.
In this video learn everything you need to know about VLOOKUP in 1 video.
Here are the topics (minute marks jump you to that part of the video):
1. VLOOKUP = V = Vertical ( min)
2. Why so common? Because everyone needs to look stuff up ( 00:43 min)
3. VLOOKUP Delivers a value to a cell: Extract Match ( 2:37 min)
4. VLOOKUP #N/A and IFERROR or IF ( 5:25 min)
5. VLOOKUP and Data Validation ( 8:10 min)
6. VLOOKUP with Named Range ( 10:34 min)
7. VLOOKUP as Formula Element ( 17:24 min)
8. HLOOKUP = H = Horizontal ( 18:25 min)
9. VLOOKUP Delivers a value to a cell: Approximate Match ( 19:40 min)
10. VLOOKUP with table on different sheet ( 24:00 min)
11. VLOOKUP with table in different workbook ( 25:56 min)
12. Show Full Record: Numbers above column header ( 27:15 min)
13. Show Full Record horizontally: COLUMNS Function ( 30:20 min)
14. Show Full Record vertically: ROWS Function ( 32:28 min)
15. Show Full Record: MATCH Functions ( 33:40 min)
16. Two Lookup Values: Add join column as first column in table ( 35:40 min)
17. Two Lookup Values: Join columns in formula and use CHOOSE function ( 38:50 min)
18. Partial Text Lookup: LEFT and SEARCH Functions ( 43:22 min)
19. Partial Text Lookup: MID Function ( 45:37 min)
20. Partial Text Lookup: RIGHT Function ( 48:08 min)
21. TRIM and VLOOKUP: Space in lookup value ( 48:57 min)
22. TRIM and VLOOKUP: Space in 1st column of table ( 51:03 min)
23. VLOOKUP with 3 lookup tables: CHOOSE Function ( 53:20 min)
24. VLOOKUP: Two Way Lookup with VLOOKUP and MATCH ( 58:36 min)
25. VLOOKUP: Variable Tax Rate, 3 VLOOKUPS in one formula ( 1:00:18 min)
26. VLOOKUP and Table feature for Dynamic ranges ( 1:05:17 min)

How To Use Excel VLOOKUP Function
Learn Excel VLOOKUP Function
VLOOKUP made easy
Excel VLOOKUP Function
via YouTube https://youtu.be/-hJxIMBbmZY

Using the Excel Average and AverageA functions

Using the Excel Average and AverageA functions

Learn how the Average and AverageA functions work in Excel.

Go to http://ift.tt/2s34X24 for more tutorials or to download the class document to follow along
via YouTube https://youtu.be/EFg7vh2WGk0

Excel 2013 Course (Free)

Excel 2013 Course

In this tutorial for Excel 2013 you will begin to learn the basics of Microsoft Excel 2013

To download the course materials, sample Excel files used in the videos and the handouts for the course, please visit – http://ift.tt/2sNnz2W
via YouTube https://youtu.be/D391EU_Yi9I

Microsoft Excel 2016 Tutorial for Beginners Part 1 Full Intro Learn How to Use Excel 2016

Microsoft Excel 2016 Tutorial for Beginners Part 1 Full Intro Learn How to Use Excel 2016
Excel 2016 Tutorial for beginners
This is a Microsoft Excel 2016 Full Tutorial for beginners & intermediate users.In this crash course Excel video tutorial you will learn how to use Excel 2016 formulas and functions, Excel 2016 new features, Excel 2016 Charts along with many other necessary features that you must know how to use when using Excel 2016.

1:38 The Excel Ribbon
2:10 Ribbon Groups and Commands in Excel 2016
2:30 Data Entry in Microsoft Excel
3:08 How to enter data in to a cell in Excel
3:22 How to enter data into the formula bar in Excel
3:50 How to Navigate through Microsoft Excel
4:24 How to navigate and select cells in Excel with keyboard
8:01 Autofill in Excel 2016
9:15 Autocomplete in Excel 2016
10:32 How to zoom in and zoom out Excel 2016
10:50 Formulas & Functions Tutorial in Microsoft Excel 2016
11:25 Addition in Excel
13:04 Autosum Function in Excel
14:03 The Sum Function in Excel 2016
15:45 Subtraction in Microsoft Excel
16:12 Multiplication in Excel 2016
17:15 The Product Funtion/Formula in Microsoft Excel
17:44 How to Find/Search for a function in Excel using the insert function command in the Function Lirbrary
19:29 Division in Microsoft Excel
19:46 the Quotient Function in Excel
21:25 Exponential formulas in Excel
22:20 Concatenation in Excel
23:06 Logical Comparison in Excel
23:08 Equal to logical comparison in Excel
24:17 Logical Comparison Greater Than in Excel
24:36 Less Than Logical Comparison
24:58 Greater than or equal to
25:42 Not Equal to Excel 2016
26:14 Combining Formulas and Functions in Microsoft Excel 2016
29:18 Cell Reference Tutorial in Microsoft Excel
29:57 Types of cell reference in Excel
30:07 Relative Cell Reference in Excel 2016
32:04 Using Autofill for formulas and cell reference
32:25 Show formula in Excel 2016
33:15 Absolute cell reference in Excel 2016
33:10 How to lock cell reference
34:40 Mixed Cell Reference locking only the row or column
39:27 How to refer to cells in another worksheet in Excel
40:06 How to refer to another workbook in Excel
40:35 How to clear/remove/delet content in a cell
40:52 Autofill dates in Excel
41:15 How to delete a row or column in Excel
46:36 How to style and Excel worksheet
46:50 How to format numbers in Excel
47:50 increase of decrease decimals in Excel
48:54 How to format dates in Excel (short date, long date)
40:32 How to resize cells columns and rows in Excel
50:35 the Today function in Excel Today()
51:54 Formattin and styling cells in Excel
52:05 How to add color fill in Excel
52:55 How to add borders in Excel
53:44 How to copy and paste a range of cells in Excel
54:23 Cell Styles in Excel 2016
55:27 Text direction and how to angle diagnal text content and numbers in Excel
57:00 How to Merge cells in Excel
57:53 Merge across in Excel
58:18 Change height or width of cell in Excel
58:49 Font Style in Excel
1:01:37 content alignment in Excel
1:02:20 Conditional Formatting in Excel 2016

via YouTube https://youtu.be/p1pZmbuygZY