Home
Search results “Excel trick magic”
Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE?
 
15:52
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use VLOOKUP when there are multiple Lookup Tables with these methods: 1. (00:15) Problem Set up 2. (01:48) IF Function 3. (05:57) IFS Function (Excel 2016 Insider Program) (Formula from XLarium at YouTube) 4. (08:47) SWITCH Function (Excel 2016 Insider Program) 5. (11:26) Names Ranges & INDIRECT Function? 6. (15:15) Summary
Views: 84506 ExcelIsFun
Advanced Excel 5 Magical Hidden Tips & Tricks To Make You Excel Expert || Best Time Saving Tricks
 
10:33
Advanced Excel 5 Magical Hidden Tips & Tricks - learn MS Excel 5 Fantastic Magical Hidden super time saving secrates Tips & Tricks which make you the master of excel. i will show you excel Best Time Saving Tips and tricks for all ms excel versions 2007 2010 2013 2016. 1. copy data automatically to another worksheet/ best hidden shortcut key we can very easily copy the data from one excel sheet to another sheet. 2. How to add numbers containing text such as 10kg 20kg 30kg 20pcs 12pcs etc. 3. how to add blank rows in large existing record. 4. how to remove blank records. 5. using excel camera. taking screenshoot using excel camera. this is the best excel hidden trick. Hope you will like the video. Thanks for watching. Deepak EduWorld के दुआरा publish की हुई playlist इस प्रकार हैं ________________________________________________ ADVANCED MS EXCEL के लिए ये playlists देखे ► https://goo.gl/A6LdZA ADVANCED MS EXCEL FORMULAS के लिए ये playlists देखे ► https://goo.gl/bbHYu2 MS WORD के लिए ये playlist देखे ► https://goo.gl/B4TeSA HTML5 & CSS3 के लिए ये playlist देखे ► https://goo.gl/2PFgs8 COMPUTER के TECHNICAL videos के लिए ये playlist देखे ► https://goo.gl/paZmpn --------------------------------------------------------------------------- --------------------------------------------------------------------------- Subscribe to my channel ► https://www.youtube.com/deepakeduworld Like my Facebook page ► https://www.facebook.com/deepakeduworld Follow on LinkedIn ► https://www.linkedin.com/in/deepakeduworld Facebook Group ► https://www.facebook.com/groups/cse92/ ********************************************** Thanks for Watching Happy Learning ER DEEPAK NEHRA [ M.Tech. ] **********************************************
Views: 431768 Deepak EduWorld
Excel Magic Trick 👍 To Make Excel Formulas Dynamically Updated Automatically 😮 | Hindi |
 
08:52
Make Excel Formulas Dynamically Updated Automatically - Deepak EduWorld will explain how you can setup your formula table to make it is dynamic. This means that whenever you add an entry or any data to an existing table, excel formulas will automatically update to include this new data. the This is a really neat trick which will help save you a lot of time when working with excel formulas on large data. See how to create a dynamic table for the excel function. How To Make excel formulas Dynamically Updated - Make auto updating formulas - with dynamic table array/range in HINDI. how to use averageif formula in hindi https://youtu.be/7Etgj4X8_Vo how to use sumif formula in hindi https://youtu.be/QcDv6Vvrcsg how to use sumifs formula in hindi https://youtu.be/rDaTTrW6N_0 how to use countif formula in hindi https://youtu.be/HgFHx17PPcc VLOOKUP VIDEO SERIES: 1. vlookup in excel with example in Hindi https://youtu.be/S2rF6bHIbdA 2. How to use vlookup for multiple values || Trick to use vlookup for large data https://youtu.be/oSE4wSy02s8 3. Vlookup in multiple sheets in excel || In Hindi https://youtu.be/fpslxPp3_Cw 4. How To Use Double VLookup in Excel-Nested vlookup https://youtu.be/REBdKbbvwG0 5. Dynamically Updated Vlookup Formula in Excel https://youtu.be/IHD6XIDrCfk 6. How To Use Vlookup With Multiple Excel Workbooks https://youtu.be/CiHIbsRM3cg 7. HLOOKUP in excel with example in Hindi https://youtu.be/DyH3PY3qbIE Hope you will like the video. Thanks for watching. Deepak EduWorld के दुआरा publish की हुई playlist इस प्रकार हैं ________________________________________________ ADVANCED MS EXCEL के लिए ये playlists देखे ► https://goo.gl/A6LdZA ADVANCED MS EXCEL FORMULAS के लिए ये playlists देखे ► https://goo.gl/bbHYu2 MS WORD के लिए ये playlist देखे ► https://goo.gl/B4TeSA HTML5 & CSS3 के लिए ये playlist देखे ► https://goo.gl/2PFgs8 COMPUTER के TECHNICAL videos के लिए ये playlist देखे ► https://goo.gl/paZmpn --------------------------------------------------------------------------- --------------------------------------------------------------------------- Subscribe to my channel ► https://www.youtube.com/deepakeduworld Like my Facebook page ► https://www.facebook.com/deepakeduworld Follow on LinkedIn ► https://www.linkedin.com/in/deepakedu... Facebook Group ► https://www.facebook.com/groups/cse92/ ********************************************** Thanks for Watching Happy Learning ER DEEPAK NEHRA [ M.Tech. ] **********************************************
Views: 35029 Deepak EduWorld
Magic in Excel | Unique Trick in Excel by The Accounts
 
10:08
Magic in Excel | Unique Trick in Excel by The Accounts ****Please Don't Forget to Subscribe My Channel***** कृपया हमारे चैनल को Subscribe ज़रूर करलें क्योंकि आप घर बैठे फ्री में Accounting और Tally, Excel, Word, PPT में मास्टर बन सकते हैं.इस Channel के माध्यम से आप GST, GST Return, TDS, Tds Return, Income Tax & Return, Payroll, BRS, Petty Cash, Credit Card, E-Return, Accounts Finalization and YouTube की पूरी जानकारी आप को मिलेगी. यदि आपके पास कोई प्रश्न और सुझाव है तो Comment Box में लिखें और आप [email protected] पर भी ईमेल कर सकते हैं। ---------------------------------------------------------------------------------- 1. Automatic GST Entry in Tally | Automatic GSTR3B & GSTR1 Return by Tally ERP | - https://www.youtube.com/watch?v=ssR8UCH7wHk 2. How to File Online GSTR-1 in Detail | विस्तार से Online GSTR-1 की जानकारी - https://www.youtube.com/watch?v=tb7C-ez7Dqg 3. How to File Offline GSTR 1 Return GSTR-1 को Offline (Tally ERP-9) कैसे फाइल करें - https://www.youtube.com/watch?v=fqqyfg5hDlw 4. How to File GSTR-3B Online in Detail | GSTR-3B ONLINE करने की पूरी जानकारी -Hindi Video - https://www.youtube.com/watch?v=rxxzYlz0HzQ 5. How to File GSTR-3B By Tally ERP9(JSON) Tally से GSTR-3B कैसे फाइल करें# Hindi Video - https://www.youtube.com/watch?v=N1J62HfscO8 6. RCM 1 | Reverse Charge Mechanism in GST | RCM in GST | RCM Accounting Entries in GST - https://www.youtube.com/watch?v=ekFPn3fShmI HRA Calculation for A.Y. 2020-21 | House Rent Allowance Calculation for F.Y. 2019-20 - https://www.youtube.com/watch?v=nzo25y2ou2w TDS 1 | What is Tax Deducted at Source | How to Deduct TDS | TDS Kiya Hai in Hindi - https://www.youtube.com/watch?v=zYvGXtfF7yk TDS 2 | Tds Journal Entries with GST | Journal Entry of TDS | Basic Concept of TDS | - https://www.youtube.com/watch?v=RfiP_MzaKAs TDS 3 | Tds Rate Chart | Tax Deducted at Source in Hindi | Tds Exemption Limit - https://www.youtube.com/watch?v=Gc9wxMeBeD4 TDS 4 | TDS in Tally ERP | TDS Entries with GST in Tally ERP | GST Entries with TDS in Tally ERP - https://www.youtube.com/watch?v=MXDd4DIbDTs Types of Account | Accounting System खातों के प्रकार और लेखांकन की प्रणाली-Hindi Video - https://www.youtube.com/watch?v=LTRtKKvhdTk Golden Rules Of Accounting क्या है ?लेखांकन के सिद्धांत क्या है# Hindi Video - https://www.youtube.com/watch?v=6QbuJ1DkTyY Accounts Journal Entries|Top 23 Voucher Entries - Hindi Video by The Accounts - https://www.youtube.com/watch?v=MseRnAGJJVo How to Prepare for Accountant Job | Accountant Banne Ke Lia Kiya Karein - https://www.youtube.com/watch?v=q6XROnvJC6o Accountant | Accountant Kaise Bante Hain | How to Become Accountant - https://www.youtube.com/watch?v=mQJF_YwZObI What is Debit and Credit in Accounting | Debit and Credit Meaning | Debit and Credit Rules - https://www.youtube.com/watch?v=MYqykRGSEBw Accounts Finalisation in Tally | Balance Sheet Finalisation in Tally | Final Account in Tally - https://www.youtube.com/watch?v=v-eCmGDBNuU Part-1 How to Make Bank Reconciliation Statement | What is Bank Reconciliation Statement | What is BRS - https://www.youtube.com/watch?v=E6k6hnvhYho Part-2 BRS Live Demo with Practical | How to Make Bank Reconciliation Statement with Practical - https://www.youtube.com/watch?v=Fiu5RQm_tWE Accountant | Accountant Kaise Bante Hain | How to Become Accountant - https://www.youtube.com/watch?v=mQJF_YwZObI How to Prepare for Accountant Job | Accountant Banne Ke Lia Kiya Karein - https://www.youtube.com/watch?v=q6XROnvJC6o Accounts Finalisation Procedure | Balance Sheet Finalisation | Finalisation of Accounts of Company - https://www.youtube.com/watch?v=W8uJOQlcaTk How to File Online GSTR-1 in Detail | विस्तार से Online GSTR-1 की जानकारी - https://www.youtube.com/watch?v=tb7C-ez7Dqg How to File GSTR-3B Online in Detail GSTR-3B ONLINE करने की पूरी जानकारी -Hindi Video - https://www.youtube.com/watch?v=rxxzYlz0HzQ How to Pay GST# Create Challan through Net Banking, Over the Counter & NEFT/RTGS - https://www.youtube.com/watch?v=7o7x6F9tnZc How to File Offline GSTR 1 Return GSTR-1 को Offline (Tally ERP-9) कैसे फाइल करें - https://www.youtube.com/watch?v=fqqyfg5hDlw #Excel Your Query- How to apply magic trick in excel excel formula Follow on:- https://www.facebook.com/TheAccountss https://www.facebook.com/shafitheaccounts https://www.instagram.com/theaccountss/
Views: 3464 The Accounts
Excel Magic Trick 432: Find & Extract Duplicate Records
 
03:28
See how to use the COUNTIF function, Sort, Advanced Filter Extract Unique Records to find and extract duplicate records from a data set.
Views: 137401 ExcelIsFun
Excel 2010 Magic Trick 670: AGGREGATE Function Array Formula #4 Extract Records 1 criterion
 
08:32
See how to use the AGGREGATE function to create a formula for extracting records with one criterion. This is a lookup problem where you want to return multiple records from one lookup value. This formula is not an array formula because it does not require Ctrl + Shift + Enter. Use AGRREGATE to Replace SMALL function in array formula. Array formulas: 1) For AGGREGATE function, Numbers come first, then divide, then criteria; 2) For IF function, Criteria come first, then comma, then numbers. AGGREGATE function joins the ranks of LOOKUP, INDEX and SUMPRODUCT as functions that can handle arrays! Download files: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm
Views: 11075 ExcelIsFun
💥 Excel Magic Trick For Every Excel User - Wildcard Characters
 
03:21
#Excel #ExcelUser #MagicTrick Excel Magic Trick For Every Excel User - Wildcard Characters is video mein Humne MS Excel ki ek Aisi magic trick ke baare me bataya Hai Jise Jankar aap wow kah uthenge Is Magic Trick Mein Humne aapko bataya hai ki Kaise aap Excel wildcard characters ka istemal Karke Bina Kisi formula Lagaye first name Se last name ko alag alag kar sakte hain Asha hai yeh video aap ko Pasand aayega Nahi Jante Hain Excel ki ek Jabardast magic trick https://www.youtube.com/channel/UCiJKjd-IglGPBrlGhehiWhA/join Facebook - https://www.facebook.com/GeekyAbhimanyu WebSite - http://mybigguide.com Like us on Facebook : https://www.facebook.com/myBigguide Follow us on Google+ : https://plus.google.com/+Mybigguidehindi Follow us on Twitter : https://twitter.com/mybigguide Android app on Play store : https://goo.gl/pNJWTv हमारे टॉप 5 वीडीयो — 1- केवल Hindi Typing वाले देखें - https://goo.gl/fKXmvw 2- Excel में IF को नहीं सीखा तो क्या सीखा ? - https://goo.gl/yPh5vb 3- Excel Expert बनना है तो ये देखो - https://goo.gl/2W66t4 4- Excel का ये Formula अापकी सारी थकान मिटा देगा - https://goo.gl/LEWFgL 5- क्‍या आपने Excel में मैक्रो (macro) सीखा ? - https://goo.gl/k2HQcm एक्सेल के सभी वीडीयो यहॉ देखें — https://goo.gl/aF43Hs एक्सेस के सभी वीडीयो यहॉ देखें — https://goo.gl/LRohxH वर्ड के सभी वीडीयो यहॉ देखें — https://goo.gl/1yKpk1 कंप्यूटर नेटवर्किंग के सभी वीडीयो यहॉ देखें — https://goo.gl/9qmhvL फोटोशॉप के सभी वीडीयो यहॉ देखें — https://goo.gl/AVsjLZ विंडोज 7 के सभी वीडीयो यहॉ देखें — https://goo.gl/z44h2d जीमेल के सभी वीडीयो यहॉ देखें — https://goo.gl/h3EZzv यूट्यूब के सभी वीडीयो यहॉ देखें — https://goo.gl/zqzoJW टेक्नोलॉजी के सभी वीडीयो यहॉ देखें — https://goo.gl/pfQKsk हर रोज नया वीडियो ट्यूटोरियल हिन्‍दी में पाने के लिये हमारा यूट्यूब चैनल सबस्‍क्राइब करें और घर बैठे ही जॉइन करें माय बिग गाइड स्‍मार्ट क्‍लास 👈 इसके अलावा अगर आप हिंदी में और भी तकनीकी जानकारी प्राप्‍त करना चाहते हैं तो आप हमारी बेवसाइट http://mybigguide.com पर जा सकते हैं और अगर आपको कुछ पूछना है तो आप हमें [email protected] पर मेल कर सकते हैै Tag - computer, internet, computer user, learn computer, hindi technology, internet user, excel user, ms office user, कम्प्यूटर, computer hindi, learn computer hindi, कंप्यूटर , my big guide, Abhimanyu Bhardwaj, माय बिग गाइड
Views: 31818 mybigguide
4 Super Cool Tricks for MS Excel | Excel Magic Trick Part 13
 
11:23
Doston is video me maine apko microsoft #Excel ki 4 #MagicalTricks ke baare me bataya hai jo apke liye bahut hi jyada useful hogi. Hope aap sabhi ko hamara ye video pasand aayega. ❤Show your LOVE by “ LIKE COMMENT & SHARE this video ” ❤Don’t forget to “SUBSCRIBE & PRESS BELL ICON” 🔥दोस्तों अगर आप मुझे सपोर्ट करना चाहते हैं तो इसके लिए आप हमारी shop ( https://www.amazon.in/shop/mylivesupport ) से कुछ भी खरीद सकते हैं ▶️WATCH ALL VIDEOS FROM BELOW PLAYLIST : ▶️Excel की मैजिक ट्रिक सीरीज यहाँ से देखें – https://goo.gl/qS296Q ▶️हमारी Top 10 वीडियो यहाँ से देखें - https://goo.gl/McEe53 ▶️Excel की सभी वीडियो यहाँ से देखें – https://goo.gl/boUo8C ▶️टेक्नोलॉजी की सभी वीडियो यहाँ से देखें – https://goo.gl/E9VyCA ▶️Visual Basic की सभी वीडियो यहाँ से देखें – https://goo.gl/W4pb5t ▶️Tally.ERP9 GST की सभी वीडियो यहाँ से देखें - https://goo.gl/CbsFhr Please support me on Social Media Like us on Facebook - https://www.facebook.com/MyLiveSupport/ Follow us on Twitter - https://twitter.com/MyLiveSupport Follow us Instagram - https://www.instagram.com/mylivesupport/ Facebook Myself - https://www.facebook.com/ChandanKumar926 YouTube - https://www.youtube.com/c/mylivesupport ----------------- Chandan Kumar -----------------
Views: 80596 My Live Support
Excel Magic Trick 1474: Excel Twinkling Christmas Tree With Star & Formulas as Presents!
 
02:06
Download Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1474.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Learn about how to create a Twinkling Christmas Tree with a Star on Top in Excel using the RAND and CHAR functions, the Conditional Formatting Scales and Conditional Formatting Icons. Happy Holidays from excelifun!
Views: 7350 ExcelIsFun
Excel 2010 Magic Trick 818: Extract Records For Each Country To A New Sheet With Formula
 
10:42
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. Array formula to Extract Records For Each Country To A New Sheet With Formula 2. Functions INDEX, MATCH, SMALL, IF, ROWS and Table Nomenclature Related video: Excel 2010 Magic Trick 818: Extract Records For Each Country To A New Sheet With Formula
Views: 19671 ExcelIsFun
Excel Magic Trick #19: Randomly Generate Words
 
03:35
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the CHOOSE and RANDBETWEEN functions to randomly generate words. See how to randomly select a Region from all your sales regions.
Views: 33581 ExcelIsFun
Excel Magic Trick 1402: Formula to Put Check Mark In Cell If Number Greater Than Zero
 
06:05
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to put a check mark in a cell when a number is greater than zero using the IF Function and the CHAR function: 1. (00:11) Introduction 2. (00:41) Insert a Symbol 3. (01:28) Generate full list of all 255 ASCII Characters using two tricks: 1) Right-click Trick to fill column with Series of numbers 1 to 255. See the CHAR Function to get the ASCII Character for a give ASCCI Code. 4. (03:48) See the CODE Function to get the ASCII Code for a give ASCCI Character. 5. (04:16) IF Function to put check mark in cell when number is greater than zero. 6. (05:32) Conclusion
Views: 25360 ExcelIsFun
Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples
 
38:13
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to: 1. (00:15) Introduction to Contains Criteria (Partial Text Criteria) in an OR Logical Test, when search for records with Multiple Contains Criteria. 2. (02:00) #1 Filter & Helper Column with Formula that uses OR, ISNUMBER & SEARCH functions. This Array Formula requires Ctrl + Shift + Enter. 3. (07:23) #2 Filter & Helper Column with Formula that uses ISNUMBER, LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter. 4. (12:11) #3 Easiest solution: Advanced Filter with Formula Criteria, where formula uses LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter. 5. (15:56) #4: Array formula #1 to count matching records using an OR Logical Test with Multiple Contains Criteria. Use SEARCH function with two different size Function Argument Array Operations that require that we flip one of the ranges using the TRANSPOSE Array Function. Also see the functions: ISNUMBER, MMULT, ROW and SUM. This Array Formula requires Ctrl + Shift + Enter. 6. (21:45) Introduction to Matrix Multiplication, the Excel MMULT Array Function and detailed explanation of the mechanics of how it works in Array Formula to add all the numbers for each row in a two-way array and produce a single column of totals. This Array Function requires Ctrl + Shift + Enter. 7. (30:18) #4: Array formula #2 to extract records. See the functions: SEARCH, TRANSPOSE, ISNUMBER, MMULT, ROW, IF, ROWS, INDEX and SMALL. This Array Formula requires Ctrl + Shift + Enter. 8. (36:52) Test Array Formulas when source data changes. 9. (37:39) Summary. Match Job Title to List of Key Words Reference Video: Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria
Views: 24501 ExcelIsFun
Excel Magic Trick #14: Amazing Find & Go To Trick!
 
03:12
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the Find command to go to all cells with a certain formatting, or even a certain function! Then it is easy to make universal changes after you have all the cells with a certain condition highlighted! This trick can speed up work and help you find hidden items that would be hard to find otherwise! Find, Go To, GoTo, Replace.
Views: 53486 ExcelIsFun
Excel Magic Trick 1127: Automatically Track Budget Start, Change & Ending Balances In Excel
 
18:14
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to automate the tracking of and the Current and Ending Amounts for Budget Categories using The Excel Table feature, Data Validation drop-down list, the SUMIFS function, and Page Setup for printing. 1. (0:08 min) Overview 2. (01:11 min) The Excel Table feature 3. (02:29) Data Validation list 4. (09:28 min) SUMIFS function and Current Balance Formula 5. (15:27 min) Page Setup Question from: Kaddee Lawrence Highline Community College Excel Budget
Views: 42443 ExcelIsFun
Excel Magic Trick 1113: Extract All Characters In Cell To Separate Cells: PPPP to P, P, P, P
 
04:31
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to Extract All Characters In Cell To Separate Cells: "PPPP" to "P", "P", "P", "P": 1. Text To Columns using Fixed Width, One Time Event 2. Formula that will update when data changes. Formula uses MID and COLUMNS functions and Mixed Cell References.
Views: 29132 ExcelIsFun
Excel Magic Trick 1287: SWITCH Function: How to Lookup Formulas, References, or Anything Else
 
17:52
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to: 1. (00:11) Intro to Exact Match lookup SWITCH Function, including a default value. SWITCH in a New Excel 2016 function. 2. (01:13) SWITCH to lookup Text Items and put a Default Value in the cell if the lookup value is not found. 3. (02:52) Compare and contract IFS function, CHOOSE function, VLOOKUP function and SWITCH function 4. (04:44) See how to use VLOOKUP and the IFNA Function with a hard coded lookup table in your formula when you have a lookup situation and you need a Default Value rather than SWITCH to make a more compact and efficient formula. 5. (06:24) When to NEVER use SWITCH 6. (06:35) SWITCH to lookup formulas. Credit Assessment formulas to gage credit worthiness. 7. (09:48) What formula do you use when you have multiple lookup tables? 8. (10:18) Multiple lookup tables example 1: CHOOSE, VLOOKUP, IFNA, and VLOOKUP. This is the method we used before we had SWITCH 9. (13:34) Multiple lookup tables example 2: SWITCH and VLOOKUP to create a much shorter formula than using CHOOSE. This example shows that SWITCH requires hard coded values in the valueN argument (which Microsoft says is a bug and will be fixed soon (this is written on 3/5/2016). 10. (15:54) Bug as of 3/5/2016 for SWITCH: ValueN argument cannot handle references in the argument to lookup references. Microsoft says they will fix this bug. 11. (17:02) Summary of uses for SWITCH Reference Videos: Highline Excel 2013 Class Video 14: AND, OR, IF Functions For AR Dept (Multiple Logical Tests) https://www.youtube.com/watch?v=SennYjw9yGQ Purchase "Office 2016 for Office 365" https://products.office.com/en-us/buy/office Go to Office Insider site to download "Office Insider Version" https://products.office.com/en-US/office-insider Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025
Views: 17517 ExcelIsFun
Excel Magic Trick #90: Consolidation
 
09:46
Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks90.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks90Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the Consolidating feature in Excel. Summarize data from a number of different tables quickly using the Consolidation Feature. See how to: 1) Name ranges, and how to edit named ranges 2) Consolidate tables from different sheets 3) Create consolidation with linked formulas 4) Create consolidated tables where the input tables are not the same size 5) See how to create a 3-D cell reference Formula and functions such as Sum and AVEARGE
Views: 197838 ExcelIsFun
Mysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)
 
01:09:27
Need to learn all about VLOOKUP? Microsoft Excel MVP & Highline College Professor, Mike “excelisfun” Girvin, presents 15 amazing VLOOKUP examples, from the basics to advanced. Download Excel START File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/StartFileMysteriesOfVLOOKUPRevealedEMT1514.xlsx Download Source file: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/SourceData.xlsx Download Excel FINISHED File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/FinishedFileMysteriesOfVLOOKUPRevealedEMT1514.xlsx In this video Learn all about VLOOKUP. Learn from Basics to Advanced. See 12 amazing examples that will help you become a VLLOKUP Excel Master! Video taught by Microsoft Excel MVP and Excel YouTuber, Mike Girvin. Topics: (00:06) Introduction 1. (01:32) VLOOKUP is everywhere (04:24) The different between Exact Match & Approximate Match Lookup 2. (06:00) VLOOKUP to Lookup Product Price (Exact Match Lookup) 3. (12:25) VLOOKUP to Lookup Straight Commission Rate (Approximate Match Lookup) 4. (18:32) Data Validation List & VLOOKUP 5. (21:45) Copy VLOOKUP Down a Column. Learn about Relative and Absolute Cell References. 6. (27:00) Dynamic Lookup Table: Excel Table feature 7. (31:47) Dynamic Data Source: Use Power Query to import Lookup Table 8. (36:14) VLOOKUP to Lookup Variable Commission Rate (Approximate Match Lookup) 9. (41:00) VLOOKUP & MATCH Function for Two-Way Lookup (Lookup Employee Information) 10. (48:02) Fuzzy Lookup = Incomplete Lookup Value 11. (51:50) VLOOKUP & IFNA Functions to Avoid Errors 12. (53:05) Partial Text Lookup & Converting Text Number to Number 13. (56:28) Avoid Zeros from VLOOKUP to Empty Cells 14. (58:27) Multiple Table Lookup with VLOOKUP and INDIRECT Functions 15. (01:04:40) Two Lookup Values (01:08:29)Summary
Views: 37721 ExcelIsFun
Excel Magic Trick #4: SUM function Keyboard Trick
 
01:43
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel! See how to add data or a formula to a range of cells by using Ctrl + Enter! Also, see the keyboard shortcut for the Auto SUM function and the Currency Format. Lightening fast keyboard short cuts to make work go faster and to show off to your boss!
Views: 69896 ExcelIsFun
Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)
 
22:32
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to Compare 2 Lists, Extract Items In List 2 That are NOT in List 1: 1. (00:12) Problem Description and Intro To Topic 2. (01:57) Example 1: Method 1: MATCH function Helper Column, Sort Z to A, Copy and Paste 3. (04:15) Example 2: Method 2: Advanced Filter and Criteria Area with Empty Field Name and Logical Formula Criteria 4. (07:59) Example 3: Method 3: Automat Process with Excel 2010 or later Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, AGGREGATE, SUMPRODUCT. This Array Formula does not require Ctrl + Shift + Enter. 5. (15:29) Why you should NOT use IFERROR on Data Extract Array Formulas 6. (17:17) Example 4: Method 3: Automat Process with Excel 2007 or before Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, SMALL, SUMPRODUCT. This Array Formula DOES require Ctrl + Shift + Enter. 7. (18:25) Example 5: Method 2: Extract E-mails in List 1 that are NOT in List 2. 8. (19:35) Caveat about Advanced Filter: Be sure to NOT have data below Extract Area (because it will be deleted when you run Advance d Filter. 9. (20:24) Example 6: How to Extract Items that are in BOTH lists: Learn how to slight change the formula for all three methods. Use ISNUMBER rather than ISNA. Related Videos for Comparing Two Lists and Extracting Records: Other Excel Methods for Comparing Two Lists: Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples) https://www.youtube.com/watch?v=9h1omv60MCA Excel Magic Trick 1229 Extract Items NOT in List 1: Conditional Format Unique Values & Sort by Color https://www.youtube.com/watch?v=E3_vMI3LjJU Excel Magic Trick 1441: Power Query to Extract Items In List 1 That Are NOT In List 2 https://www.youtube.com/watch?v=JztEKJ-XkCU Excel Magic Trick 1442: DAX Query & EXCEPT Function to Extract Items In List 1 That Are NOT In List 2 https://www.youtube.com/watch?v=lLMXFLr9Aeg
Views: 46904 ExcelIsFun
Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)
 
01:09:39
Download file: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT778.xlsx There are really about 30 example in this long video. INDEX and MATCH How To: 1 MATCH Functions Basics (about 0:46 minute mark) 2 INDEX Basics: Two way lookup (about 9:05 minute mark) 3 One way lookup vertical (lookup Left) (about 13:07 minute mark) 4 One way lookup horizontal (about 14:50 minute mark) 5 One way lookup with lookup column and match column orientated differently (about 17:51 minute mark) 6 INDEX & MATCH to do approximate lookup on table sorted Descending (about 19:15 minute mark) 7 Lookup whole row (about 21:41 minute mark) 8 Lookup whole column (about 24:49 minute mark) 9 Lookup Cell Reference (about 26:00 minute mark) 10 Create Dynamic Range For Chart (about 26:00 minute mark) 11 Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark) 12 Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark) 13 INDEX and ROWS to Filp a Table (about 47:20 minute mark) 14 Get Amount in Next Bracket (about 50:34 minute mark) 15 Select a Random Value (about 53:18 minute mark) 16 Lookup first non-blank value (about 54:24 minute mark) 17 Handle arrays that require Ctrl + Shift + Enter CSE (about 54:24 minute mark) 18 Basic lookup tool used in complex array formulas for extracting data (about 59:59 minute mark, 1 hour 1 minute 10 seconds)
Views: 679620 ExcelIsFun
Excel Magic Trick # 10: 3 types of Averages!
 
04:08
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel! 3 types of Averages! See how to use the AVERAGE, MEDIAN and MODE functions in Excel. Also see how to name a cell range and how to sort to verify that the functions worked correctly.
Views: 50028 ExcelIsFun
Excel Magic Trick 1481: Multiple Table Lookup: SWITCH or INDIRECT Function? VLOOKUP too
 
11:39
Download Files: Start Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1479-1481.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htmLearn about how to In this video learn how to how to perform lookup from multiple tables. Learn two methods: 1) VLOOKUP and SWITCH Function or 2) VLOOKUP and INDIRECT Function with Defined Names. 1) (00:01) Introduction 2) (01:30) VLOOKUP and SWITCH Function (New Excel 2016 Office 365 Function) 3) (07:20) VLOOKUP and INDIRECT Function with Defined Names. 4) (11:06) Summary
Views: 11996 ExcelIsFun
Excel Magic Trick #11: How Good Is Your Average?
 
05:00
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel! Learn 3 types of Variation measures including Standard Deviation. See how to determine whether your average fairly represents its data points and how spread out the data points are! See how to use the functions: STDEV, VAR and AVEDEV. Also, see how to name multiple ranges with just one keyboard shortcut!
Views: 43469 ExcelIsFun
Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria
 
10:12
Create a formula to extract part of a table from a larger table. Similar to a query in Access, but with a array formula in Excel that uses the functions: INDIRECT, IF, ROWS, COUNTIF, INDEX, SMALL, and ROW. This formula will extract records that meet one condition (criteria criterion) and list them in order with no extra rows between records. We will also use the INDIRECT function to save on formula creation time. Related Videos: YTLE#84: A Few Data Validation Tricks Excel Magic Trick #180: COUNTIF function formula 21 Examples 04 Excel Series: Names 1-12 Excel Name Trick #7: INDIRECT function, Names and LOOKUP 05 Excel Series: Array Formulas 1-14 Various INDEX Lookup w TRUE/FALSE criteria "Reverse Lookup" Excel Magic Trick 186 Dynamic Formula Extract Data 2 criteria Excel Magic Trick #187: Unique List w Dynamic Formula Excel Magic Trick #81: Advanced Filter AND OR Data Extract
Views: 147461 ExcelIsFun
Excel Magic Trick # 2: Chart Keyboard Trick
 
02:58
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel 2007 Chart! See how when formula input data is changed the formulas in the Table and the Chart update!! Plus, cool "Create Chart" keyboard shortcut.
Views: 98378 ExcelIsFun
Excel 2010 Magic Trick 662: AGGREGATE function Ignores Hidden Rows for Calculations
 
05:20
Learn about the new Excel 2010 AGGREGATE function that can make calculations that ignore hidden rows. For example you can add or average while ignoring values in hidden rows. In addition you can ignore both Hidden Rows and Error Value. SUBTOTAL function only has 11 functions, whereas the AGGREGATE has 19 plus options to ignore errors, hidden rows and nested Subtotal or AGGREGATE functions. Download files: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm
Views: 11259 ExcelIsFun
Excel Magic Trick 1 Bangla: Formula and Cell References
 
22:17
Excel magic trick bangla is very important tutorial for excel learner. In excel 2007 most of the formulas are very powerful. In this tutorial we'll learn cell, cell reference, relative and absolute cell reference, create a formula in excel tables, formula based on cell reference, fill handle, keyboard shortcut of auto sum function etc.
Views: 20052 Excel Fx
Excel Magic Trick # 3: AVERAGE & Go To Blanks
 
03:30
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel! Learn how to use the Ctrl + Enter keyboard Trick to enter formulas or data into many cells simultaneously. Also, learn how the AVERAGE function ignores Blanks, but not zeroes! And finally, learn about the amazing GoTo feature to select all Blanks in a range!
Views: 92309 ExcelIsFun
Excel Magic Trick 498: 6 PivotTable Calculation Tricks (Pivot Table)
 
08:21
Use PivotTable to Group Numbers, calculate Frequency, % Total, Cumulative Total, % Cumulative Total. See how to: 1. PivotTable (Pivot Table) 2. Group Integers in PivotTable 3. Count numbers 4. use "% of Column Total" in "Value Field Settings" "Show values as" tab 5. use "Running Total in" in "Value Field Settings" "Show values as" tab 6. use "% Running Total in" in "Value Field Settings" "Show values as" tab, which is new to Excel 2010 New Excel 2010 PivotTable Pivot Table Feature "% Running Total in. Also see this video: Excel Statistics 26: Cumulative Freq. Dist w Pivot Table (Running Total)
Views: 121777 ExcelIsFun
Excel Magic Trick #233: 2 Business Math Tricks
 
07:58
See how to create an efficient Checkbook Balance Formula and see when to use ROUND on a Sales Invoice. Learn how to create a checkbook balance formula 13 times faster than most people. Learn when you must use the ROUND function and when you dont have to.
Views: 30788 ExcelIsFun
Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet
 
08:17
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to: 1. Select From Drop Down and Pull Data From Different Sheets using Data Validation List, the INDIRECT Function, and the join symbol ampersand & 2. Learn about syntax for Sheet References 3. INDIRECT Function 4. Data Validation Drop Down List
Views: 495597 ExcelIsFun
Excel Magic Trick 471: Sort Horizontal With Formula 3 Examples
 
04:48
See how to Sort Horizontally with 3 different formulas that uses LARGE, SMALL, COLUMNS, IFERROR and IF functions.
Views: 18009 ExcelIsFun
Excel Magic Trick 1425:  Convert 2-Way Table to Single Column Table with INDEX & Number Incrementor
 
07:03
Download File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1425-1426.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm See how to use INDEX function & Number Incrementors to Convert 2-Way Table to Single Column Table: 1. (00:11) Introduction 2. (00:52) Copy Paste Trick to Convert 2-Way Table to Single Column Table 3. (01:46) Number Incrementor for Row Number using ROWS and ROUNDUP Function 4. (04:00) Number Incrementor for Column Number using ROWS and MOD Function 5. (05:22) INDEX Function to extract columns in Two Way Table into a Single Column 6. (06:38) Summary
Views: 5056 ExcelIsFun
Excel Magic Trick 1443: DAX Formula for Sales Per Working Day: Power Pivot or Power BI Desktop
 
21:12
Learn how to calculate Sales per working day based on a Data Model with Sales and Data Table. See the DAX Functions: CALCULATE, COUNTROWS, SUM, CROSSJOIN, VALUES, SUMX, IF, FILTER and MAX. Learn how to create an internal table using the CROSSJOIN function, that SUMX can iterate over to calculate the Correct number of working days for each date period on the Report Filter Context. Download File: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1443Start.xlsx Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1443Finished.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm
Views: 6427 ExcelIsFun
Excel Magic Trick 518: Show Multiple Vendor Names Associated With Low Bid Array Formula INDEX
 
08:06
See how to use INDEX, SMALL, IF, COLUMN, COLUMNS, COUNTIF and MIN functions to extract and display multiple vendor names when multiple vendors are ties for low bid.
Views: 6274 ExcelIsFun
Excel 2010 Magic Trick 661: AGGREGATE function Contains 19 different functions
 
09:55
Learn about the new Excel 2010 AGGREGATE function that can calculate these 19 different functions depending on what number you put into the 1st argument of the AGGREGATE function. This is an improvement over the SUBTOTAL function which only has 11 functions. The functions are: AVERAGE -- find the mean of a data set in a column. COUNT -- count the number of numbers in a column. COUNTA -- count the number of non-empty cells in a column. MAX -- find the max of a data set in a column MIN -- find the minimum value in a data set in a column PRODUCT -- Multiply all the values in a column. STDEV.S -- standard deviation of a sample set STDEV.P-- standard deviation of a population SUM -- add values in a column VAR.S -- variance for a sample data set. VAR.P-- variance for the population. MEDIAN -- median value positionally in the middle. MODE.SNGL -- New Excel 2010 MODE function -- the one that occurs most frequenctly. LARGE -- find the 2nd, or 3rd, etc. Largest value in a data set. SMALL -- find the 2nd, or 3rd, etc. smallest value in a data set. PERCENTILE.INC -- Percentile the old Excel method (inclusive) QUARTILE.INC -- Quartile the old Excel method (inclusive) PERCENTILE.EXC -- Percentile the new Excel method (exclusive) QUARTILE.EXC -- Quartile the new Excel method (exclusive) Download files: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm
Views: 40680 ExcelIsFun
Excel 2010 Magic Trick 817: Extract Unique List of Values For Dynamic Data Validation Drop Down List
 
11:16
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to use a helper column to Extract Unique List of Values For Dynamic Data Validation Drop Down List. 2. See the functions, COUNTIF, IF, SUM, INDEX, MATCH, ROWS. 3. Non-array formula method Related video: Excel 2010 Magic Trick 818: Extract Records For Each Country To A New Sheet With Formula
Views: 43517 ExcelIsFun
Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions
 
06:24
See how to download and install MoreFunc. See new functions like: EVAL evaluates a text string FILENAME gives workbook name FILENAME gives file path FORMULATEXT shows formula in cell as text MRAND randomly generates numbers without repeating numbers. Series of random integers without repetitions COUNTDIFF counts unique records in a list MCONCAT concatenates arrays NBTEXT converts numbers to words
Views: 35899 ExcelIsFun
Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)
 
01:09:30
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. Above Average for Sales (1:24 minute mark) 2. Below Average for Sales ( 3:18 minute mark) 3. Top 5 ( 3:30 minute mark) 4. Greater than 600 ( 4:09 minute mark) 5. Data Bars ( minute mark) 6. Color Scale ( 4:58 minute mark) 7. Icon (6:00 minute mark) 8. Highlight Sales that are on 5/25/2011 ( 7:19 minute mark) 9. Highlight Entire Row for invoices sold on 5/25/2011 ( 12:30 minute mark) 10. Highlight Entire Row for invoices #10501 ( 17:00 minute mark) 11. Highlight entire row when sales are above average ( 18:23 minute mark) 12. Highlight every other row with MOD function ( 19:55 minute mark) 13. Highlight Row In Checkbook when Reconciled ( 23:43 minute mark) 14. Highlight Entire Row for the 3 Biggest Values ( 24:50 minute mark) 15. Highlight Entire Row for Sale Rep Moe AND Sales Greater Than500 (AND Criteria) AND function ( 28:28 minute mark) 16. Highlight Entire Row for Sales Rep Moe or Joe (OR Criteria) OR function ( 30:44 minute mark) 17. Highlight Entire Row that contains a date between two dates ( 32:10 minute mark) 18. Highlight Entire Row based on both AND Criteria and OR Criteria AND and OR function together ( 33:33 minute mark) 19. Highlight entire row based on Approximate Value VLOOKUP function ( 36:27 minute mark) 20. Highlight entire column based on Exact Match (36:27 minute mark) 21. Highlight Intersecting Value based on both an approximate match for row header and Exact Match for column header (36:27 minute mark) 22. Highlight row based on two row headers ( 44:57 minute mark) 23. Highlight entire column based on Exact Match (44:57 minute mark) 24. Highlight Intersecting Value based on row header and column header Exact Match (44:57 minute mark) 25. Gantt Chart for Days (48:41 minute mark) 26. Highlight Weekends and Holidays NOT WORKDAY.INTL NETWORKDAYS.INTL function (48:41 minute mark) 27. Data Validation Custom Formula for preventing Sunday and Holiday Dates to be entered in cell (48:41 minute mark) 28. Highlight Duplicates: Built In Feature ( 01:04:54 minute mark) 29. Highlight Duplicates Formula ( 01:05:10 minute mark) 30. Highlight Row when Actual Exceeds Budgeted ( 01:07:20 minute mark) Excel Conditional Format, excel Conditional Formatting
Views: 289864 ExcelIsFun
Excel Magic Trick 533: Date/Time Calculation excluding Holidays & Weekends
 
04:52
See create a formula to calculate the Difference Between Start Date/Time & End Date/Time excluding Holidays and Weekends. See the Functions NETWORKINGDAYS and MOD.
Views: 44511 ExcelIsFun
Excel Magic Trick 520: FIND & SEARCH Functions
 
09:54
Learn about FIND and SEARCH functions: 1.Both functions find the position of a substring in a string the position of some characters within a different set of characters. For example FIND(A,ISAAC) = 3, SEARCH(A,ISAAC) = 3 because the first A is the third character in ISAAC. 2.FIND is case sensitive and does not allow wildcards such as * (1 or more characters) or ? a single character. 3.SEARCH is NOT case sensitive and it accepts wildcards. 4.Use LEFT FIND and SERCH to extract first names 5.See how to do Fuzzy Math or Approximate Lookup using LOOKUP, SEARCH, FIND, 2^15
Views: 114978 ExcelIsFun
Excel Magic Trick 1344: Conditional Formatting the Entire Row in an Excel Table: Totally Dynamic!!
 
06:02
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use a formula to apply Conditional Formatting to apply formatting to an entire row based on a condition in an Excel Table. This video shows the Excel Table Feature which allows Conditional Formatting to be applied to new records that are added to the Excel Table. See how to use Mixed Cell References in a formula. See how to use the New Formatting Rule Dialog Box.
Views: 10395 ExcelIsFun
Excel Magic Trick 377: Add Every Other Number In Range of Cells
 
04:30
See how to add every other number with the SUMPRODUCT, MOD and ROW functions in one formula.
Views: 14185 ExcelIsFun
Excel Magic Trick 1541: Add Sales Between Upper & Lower Date-Time with SUMIFS
 
07:09
Download Excel Start Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1538-1542.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to Add Sales Between Upper & Lower Date-Time with SUMIFS. Learn about Date and Time Values in Excel.
Views: 3878 ExcelIsFun
Excel Magic Trick #106: Gantt Chart for Daily Schedule
 
04:25
See how to use Conditional Formatting, the AND function, and cell references to create a cell Gantt Chart for a daily schedule.
Views: 73368 ExcelIsFun
Excel Magic Trick #247: Add Leading Zeroes w Formula
 
02:41
See how to add leading zeros to a text string with an array formula. See the functions IF, LEN, MAX and REPT in one array formula. The LEN function counts how many characters there are in a text string The MAX function finds the biggest value amongst a list of values The REPT function repeats a text string a specified number of times Array Formulas are entered with the key strokes: Ctrl + Shift + Enter.
Views: 7569 ExcelIsFun
Excel Magic Trick 514: Conditional Formatting Duplicate Values (3 Methods)
 
05:00
Methods: 1) Excel 2007 built-in method, 2) COUNTIF functions, 3) COUNTIF function for whole row See how to In Excel 2007 / 2010 use Conditional Formatting: 1) Highlight cells with seat names 2) On the Home ribbon, go to the Styles group, then to Conditional Formatting, then to Highlight Cells Rules, Then to Duplicate Values In Excel 2003 and earlier use Conditional Formatting 1) Format menu, Conditional Format 2) Select Is Formula from first textbox 3) Enter formula: =COUNTIF($A$1:$A$8,A1) Greater than 1 (this is simple example where names are in range A1:A8. 4) add formatting
Views: 61660 ExcelIsFun
Excel Magic Trick 1338: Power Query: Unpivot 12 Cross Tabulated Tables into One Proper Data Set
 
24:00
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See 2 Examples of how to use Power Query to Convert 12 Individual Cross Tabulated Tables into One Proper Data Set: 1. (00:15) Introduction 2. (01:14) Example 1: Start with 12 Cross Tabulated Excel Tables (Tables set up as Official Excel Tables using Excel Table feature). See the Power Query Function: Table.UnpivotOtherColumns 3. (13:46) Example 2: Start with 12 Cross Tabulated Tables. See the Power Query Function: Table.UnpivotOtherColumns & Table.PromoteHeaders 4. (23:30) Summary
Views: 13825 ExcelIsFun