Home
Search results “Magic trick in excel”
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: 77008 ExcelIsFun
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: 27064 Deepak EduWorld
Excel में Hard Work नहीं Smart Work करो | Excel Magic Tricks Part 8
 
06:45
Doston is video me maine apko bataya hai kis tarah aap #excel ki 3 Magic Tricks ka use karke apne office work ko kafi aasan bana sakte hai. Is video me aap sikhenge 1) Multiple invoice or Voucher Generate karna kucch hi second me 2) Multiple blank row ko delete karna 1 click me 3) Lower Proper aur Upper formulas ka use karna Hope aap Sabhi ko hamari #excelmagictrick series pasand aa rahi hogi. LIKE | COMMENT | SHARE | SUBSCRIBE & PRESS BELL ICON 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: 19290 My Live Support
Excel Tutorial | 20 Tricks & Shortcuts That Can Make Anyone An Excel Expert
 
13:24
After Microsoft enhanced Excel to the 2013 version, it offered more surprises than ever. In order to deal with tons of big data, you can’t ignore the important role Excel plays in daily work. However, both for beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked. Here are 20 useful Excel spreadsheet secrets you may not know. Please note that all these functions are based on Microsoft Excel 2013.
Views: 1062518 Excel to Excel
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: 42892 ExcelIsFun
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: 6655 ExcelIsFun
Excel Magic Trick 1107: VLOOKUP To Different Sheet: Sheet Reference, Defined Name, Table Formula?
 
09:02
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do a VLOOKUP to a different Sheet using: 1. Sheet References 2. Defined Names 3. Table Formula Nomenclature (Structured References)
Views: 629663 ExcelIsFun
Excel Magic Trick 1012: Automatically Add All Subtotals In A Column (6 Methods)
 
07:23
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See 6 methods to add all the subtotals in a column: 1. Manually with the SUM function 2. SUM function divided by 2 SUM/2 3. Array Formulas that uses SUMPRODUCT and the New Excel 2013 function ISFORMULA 4. SUBTOTAL if subtotals were created with SUBTOTAL function 5. AGGREGATE if subtotals were created with SUBTOTAL function 6. Subtotal feature
Views: 30942 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: 22118 ExcelIsFun
Excel Magic Trick #20: Lightening Fast Copy Trick!
 
01:44
Download Excel workbook: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the fill handle to copy a whole table or column with formatting, numbers and formulas to many other areas instantly! The trick is to leave one row of blanks below your table!
Views: 45327 ExcelIsFun
Excel Magic Trick 1281: TEXTJOIN & CONCAT New Excel 2016 functions (14 Examples of Joining Items)
 
10:54
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn about the new Excel 2016 functions TEXTJOIN and CONCAT. Compare and contrast these newer functions to the older methods of using the CONCATENATE function and the Ampersand. See some amazing new and more efficient uses that TEXTJOIN and CONCAT have over the earlier methods. 1. (00:47) Ampersand Example: Join First, Middle and Last name when there are no empty cells. 2. (01:18) CONCATENATE Function Example: Join First, Middle and Last name when there are no empty cells. 3. (02:50) CONCAT Function Example: Join First, Middle and Last name when there are no empty cells. 4. (03:30) TEXTJOIN Function Example: Join First, Middle and Last name when there are NO empty cells. Learn about the delimiter argument, ignore_empty argument, text1 argumnet, text2 arguments. 5. (04:34) TEXTJOIN Function Example: Join First, Middle and Last name when there ARE EMPTY CELLS. 6. (05:27) CONCAT Function Example: Join three elements to make up Product Code. 7. (05:56) CONCAT Function Example: Join single cell, delimiter and a range of cells to create a Product Code. 8. (06:43) Ampersand Example: Join three items with two different delimiters to create a Product Code. 9. (07:11) TEXTJOIN and Array Constant Example: Join three items with two different delimiters to create a Product Code. 10. (07:56) TEXTJOIN and Range of Cells that contains different delimiters: Join three items with two different delimiters to create a Product Code. 11. (08:19) TEXTJOIN function to join 3 items with a delimiter to create plywood label 12. (08:39) Ampersand Example: Join 20 e-mails with a semi-colon as a delimiter so we can past e-mails into E-mail To textbox so send out multiple e-mails. 13. (09:24) TEXTJOIN Example: Join 20 e-mails with a semi-colon as a delimiter so we can past e-mails into E-mail To textbox so send out multiple e-mails. 14. (09:53) TEXTJOIN to join records from a database with a comma delimiter to create single column that contains records. 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: 40992 ExcelIsFun
Excel Magic Trick # 10: 3 types of Averages!
 
04:08
Download Excel workbook: 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: 48412 ExcelIsFun
Excel Magic Trick 1249: Build Database with Excel 2016 Table feature & VLOOKUP to Get Invoice Detail
 
25:46
Download File: https://people.highline.edu/mgirvin/excelisfun.htm **Note; The word "Database" is used loosely in this video. We use it just to mean we have a dynamic table to store raw data. Overview of video: 1) Build Invoice Database with Excel 2016 Table feature 2) Learn how Excel Table feature automatically carries formatting and formulas to new records 3) Learn how to use Table Formula Nomenclature / Structured References for Excel Table formulas 4) Learn about Dynamic Ranges with Excel Table feature 5) VLOOKUP and MATCH functions to lookup Invoice Details Full Detailed list of what is in video including time hyperlinks: 1) (00:11) Look at finished database and VLOOKUP formula to understand what the goal of the video is. 2) (02:00) Database must be a Proper Data Set: 1) Field Names in First Row, 2) Records in subsequent Rows, 3) Empty Cells or Excel Column/Row Headers Around Data Set 3) (03:07) Add Bold Format to Field Names / Column Headers so database interprets headers correctly 4) (04:42) Custom Date Format to Date Field. This is carried forward to new records in Excel Table Database. 5) (05:58) Add word wrap to Product Description. This is carried forward to new records in Excel Table Database. 6) (06:05) Add Number Formatting: Currency and Percentage to number fields. This is carried forward to new records in Excel Table Database. 7) (06:58) Add formula for Amount owed after discount, tax rate and shipping. See the ROUND function. This is carried forward to new records in Excel Table Database. 8) (10:05) Convert Proper Data Set to Excel Table with Ctrl + T. Excel Table feature is Excel’s Database feature. 9) (11:32) Add new column to database for Balance Due. Learn about Table Formula Nomenclature / Structured References for Excel Table formulas. 10) (11:53) Name the Table. 11) (14:30) Add new records to test database. 12) (16:39) Add Proper Field names using Table Formula Nomenclature / Structured References for Excel Table formulas in the lookup area. Learn about relative references in Table Formula Nomenclature / Structured References for Excel Table formulas. 13) (18:50) Add Data Validation Drop Down List for invoice lookup. 14) (19:44) Create VLOOKUP and MATCH Lookup formula to lookup Invoice Detail. Learn about how to lock (absolute) references in Table Formula Nomenclature / Structured References for Excel Table formulas. 15) (24:46) Test database and lookup formulas. 16) (25:21) Summary and Conclusion For more videos about The Excel Table feature and Table Formula Nomenclature (Structured References): https://www.youtube.com/playlist?list=PLrRPvpgDmw0kmoAhXamTJw6XkebJ6gCxg
Views: 89968 ExcelIsFun
Excel Magic Trick #213: Dynamic Data Extract w Formula
 
09:20
See how to create a non-array formula to extract data dynamically with two formulas: 1) COUNTIF and Ampersand and 2) IF, ROWS, VLOOKUP and COLUMNs functions in one formulas See how to create an Excel Table List with a keyboard shortcut.
Views: 30857 ExcelIsFun
Excel Magic Trick 362: 12 Amazing Counting Formulas
 
16:45
See the functions, FREQUENCY, SUMPRODUCT, COUNTIF, COUNTA, COUNT, ISNUMBER, ROWS, COUNTDIFF, UNIQUEVALUES functions. MoreFunc Add-in 1)Count cells in range 2)Count not empty 3)Count not blank 4)Count words 5)Count numbers 6)Count unique items 7)Count duplicate items 8)Count unique numbers in a column with numbers and text 9)More Related video: Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions
Views: 42289 ExcelIsFun
Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions
 
04:30
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do a lookup for Pictures in Excel 2007 using: Data Validation Dropdown List INDEX and MATCH Functions Defined Names Excel 2007 Hint (from Joe): Insert Bitmap Image (Insert Ribbon Tab, Text group, Object) instead of a picture and then put formula in Bitmap Image Also see this video: Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?: Excel Magic Trick 637: Lookup A Chart -- Defined Names, INDIRECT function and Formulas In Pictures
Views: 57070 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: 22919 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: 665203 ExcelIsFun
Excel Magic Trick 1499: VLOOKUP or INDEX Joins to Create Transaction Description (3 Examples)
 
10:10
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1499-1500.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to create a description for a transactional data set that requires that we lookup items to join with the Transaction Number. See how to use the Ampersand, the join operator, and how to use the lookup functions VLOOKUP function and INDEX & MATCH functions. 1. (00:06) Introduction 2. (01:20) Two VLOOKUP functions in single formula solution 3. (04:33) Join Column in Lookup Table Solution (and one VLOOKUP) solution 4. (05:52) Array Formula with INDEX and MATCH functions with joined lookup columns in an Array Operation and an Array Formula. 5. (09:32) Summary
Views: 5483 ExcelIsFun
Excel Magic Trick 1515: Yearly Income Statement Using SUMIFS Function, and NO Merge & Center
 
06:46
Download Excel START File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1515Start.xlsx Download Excel FINISHED File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1515Finished.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video see how summarize the monthly Net Income Statement numbers into a Yearly Net Income Statement using the SUMIFS function. In the process, we will have to fix the Net Income Template and remove vthe Merge and Center feature and replace it with Center Across Selection.
Views: 5805 ExcelIsFun
Excel Magic Trick 1056: Excel 2013 Slicer Formulas
 
12:45
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to add Slicers to Excel 2013 Tables and then: 1. Slicers for Total Row to calculate MIN value for two criteria 2. Slicers for SUBTOTAL function to calculate MIN value for two criteria 3. Slicers for AGGREGATE function to calculate MIN value for two criteria 4. SUMPRODUCT, SUBTOTAL and OFFSET Function to create formula to multiply filtered columns based on Slicer Criteria
Views: 46210 ExcelIsFun
Excel magic trick 49 bangla - Page Setup
 
25:40
Excel magic trick bangla is one of the best bangla Excel Learning Channel. I’ve already published 58 videos about excel. This is another Excel tutorial named “Excel magic trick 49 bangla – Page Setup”. In this tutorial I have tried to discuss about Excel magic trick 49 bangla, Page Setup bangla, Page Setup in Excel bangla, Excel Page Setup bangla, Microsoft Excel Page Setup bangla, Page Setup in Page Layout Ribbon, Page Setup by Page Tab, Page Setup by Margins Tab, Page Setup by Sheet Tab, Page Orientation Portrait and Landscape, Page Scaling, Adjust to 100% normal size, Fit to 1 page(s) to 1 tall, ISNUMBER Formula bangla, Select paper Size while page setup, Print Quality 600 dpi, First page number Auto, etc. This function also helps us to know Center on page - horizontally and vertically, Page Setup Print Area, Rows to repeat at top, Columns to repeat at left, Page Setup Print Gridlines, Page Setup print Black and White, Page Setup print Draft Quality, Page Setup Print row and column headings, Page order Down then over, Page order Over then down, Excel page margin, Excel page orientation, Excel page layout tutorial, etc. This bangla tutorial about Page Setup in excel is most important in our daily life. This lesson helps us to make our work easy. This is very important tutorial about excel. Watch my all videos of excel. The links are as follows: My facebook group name: Dreamy YouTubers Link: https://www.facebook.com/groups/385939385101685/ Subscribe my YouTube Channel: Excel Fx https://www.youtube.com/channel/UCeBDjS5POmGC0ZHnVaRDiFQ Or https://www.youtube.com/ExcelFx To see my play list (Learning Excel): https://www.youtube.com/watch?v=GVzNj9SJZt8&list=PLoHV0rcjPlKL6tGmR9x6cDTaQHuwpON78 Twitter: https://twitter.com/MasumParvejMP
Views: 35497 Excel Fx
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: 16147 Excel Fx
Excel Magic Trick 167p1 IF function formula: 12 Examples!
 
10:31
Part 1. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how the IF function can be used in formulas. Also see these functions: AND, OR, SUM, AVEARAGE, STDEVP, and VLOOKUP. See how to use the IF function to put: 1 of 2 numbers in a cell or formula. 1 of 2 words in a cell or formula. 1 of 2 text strings in a cell or formula. 1 of 2 formulas in a cell or formula. 1 of 2 functions in a cell or formula. 1 of 2 cell ranges in a cell or formula. How to have more than one IF (Nested IF Ifs). When to switch to the VLOOKUP function. How to build multiple criteria formulas with the IF function. How to use the AND function with the IF function. How to use the OR function with the IF function. Excel If statement Excel Magic Trick 165p2 MACRO Advanced Filter Extract B-days Other amazing videos at YouTube: Excel Trick, Making a Gantt Chart with Excel, Recording Macros - Excel 2003, VLOOKUP in Excel 2007.
Views: 292690 ExcelIsFun
Excel Magic Trick 1114: Formula For Sequential & Repeating Numbers: 18400 1, 18441 1, 18442 2...
 
06:31
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to create formula for Sequential & Repeating Numbers: 18400 1, 18441 1, 18442 1, 18443 2, 18444 2, 18445 2, 18446 3.... See formula that uses the functions ROWS, INT and the join symbol Ampersand & Formula element to create sequential numbers like 18440, 18441, 18442. Formula element to create repeating numbers like 1, 1, 1, 2, 2, 2, 3, 3, 3.... Here is the direct link to the MOST amazing article ever written about speed, Macro for timing and Volatile functions (by Charles Williams): http://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx Szilvia "High Priestess of Excel" History of Excel: https://www.youtube.com/watch?v=yltAQQuCwgY
Views: 15737 ExcelIsFun
Excel Magic Trick 296: Conditional Format Intersection Row & Column
 
06:11
See how to add Conditional Formatting to a row, a column and the intersection of the two with three different colors. See three TRUE FALSE formulas for highlighting the whole row, the whole column and the intersection of the two. Mixed Cell References are the key the three TRUE FASLE formulas
Views: 39627 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: 118527 ExcelIsFun
Excel Magic Trick #4: SUM function Keyboard Trick
 
01:43
Download Excel workbook: 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: 68168 ExcelIsFun
Excel Magic Trick 627: FREQUENCY Array Function (10 Examples)
 
51:50
Topics about FREQUENCY array function is this video: 1. Basics of FREQUENCY function -- see all aspects of how this function works (00:34 mins) 2. Create a Vertical Frequency Distribution ( 00:34 mins) 3. Create a Horizontal Frequency Distribution ( 06:44 mins) 4. Count unique numbers in a column ( 9:39 mins) 5. Count unique numbers in column with criteria in a second column ( 13:43 mins) 6. Count unique items in a column (numbers or text) ( 17:57 mins) 7. Count unique items in a column that have blank cells ( 21:32 mins) 8. Count unique items in a column including items with asterisks and that have blank cells ( 27:23 mins) 9. Count unique items (numbers or text) in column with criteria in a second column ( 34:46 mins) 10. Extract Unique List of Items from a column with a formula ( 38:02 mins) 11. Extract Unique List of Items from a row with a formula that requires the use of the TRANSPOSE function because the FREQUENCY function returns a vertical array and not a horizontal array ( 42:48 mins)
Views: 24626 ExcelIsFun
Excel Magic Tricks # 1: Formulas & Cell References
 
04:49
Download Excel workbook: http://people.highline.edu/mgirvin/excelisfun.htm See Excel Magic! See how to build your spreadsheet smartly, so it updates automatically. See how to create formulas with cell references!
Views: 268415 ExcelIsFun
Excel Magic Trick #200: Excel Table Formula Nomenclature
 
09:18
See how to use the new Excel 2007 Table Nomenclature in formulas. See 9 examples of how to create formulas with Table Nomenclature. See how the Table Nomenclature allows you to create formulas on different sheets without having to use sheet references in formulas. Learn about Field Names in formulas as well as the All, Header, Totals, and This Row aspects of the Excel 2007 Table Nomenclature. See how to Name A Excel 2007 Table. See How To Create An Excel 2007 Table.
Views: 27192 ExcelIsFun
Excel Magic Trick 1346: Array Formula: Split Master Table into Sub Tables, Each Product on New Sheet
 
22:05
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use formulas to extract records from a Master Table to Sub Tables on Each Excel Worksheets for each product in the Master Table: 1. (00:17) Introduction. Look at Dynamic End Result Solution. 2. (01:28) Table Formula Nomenclature (Structured References) and how they ract when they are copied. 3. (02:35) Formula to Extract Sheet Name. See the functions: CELL, REPLACE and SEARCH. 4. (04:42) Formula to Count the number of records in the Master Table for each product. See the function COUNTIFS. 5. (05:57) Array formula to extract records based on Criteria. See the functions: IF, ROWS, INDEX, AGGREGATE, ROW and a number of Array Operations. 6. (17:25) Add Formula to calculate total revenue. 7. (19:07) How to Quickly Copy Worksheets using a Keyboard and Mouse. 8. (20:25) Test Where Master Table and Sub Tables are Dynamic and will update when new records are added. 9. (21:06) Summary & Considerations for Advantages and Disadvantages to using Array Formulas. Extract Product Records From Master Table to Multiple Sheets (Sub Tables). How to Retrieve Multiple Records from One Lookup Value. Return Multiple Records from One Lookup Value. Other Similar Videos: Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through Excel Magic Trick 1347: Power Query: Split Master Table into Sub Tables, Each Product on New Sheet
Views: 17917 ExcelIsFun
Excel Magic Trick #22: NORMDIST function for Probability
 
07:36
Download Excel workbook: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the NORMDIST function to calculate probabilities for a population with a normal (bell) distribution! You even get to see the four types of situations that you most commonly encounter -- plus visual pictures of each situation to help with the concept!
Views: 81132 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: 10957 ExcelIsFun
Excel Magic Trick 1505: Fill Data Automatically in Excel with Magic Fill Handle
 
01:12
Click Link to Watch Video: https://support.office.com/en-us/article/fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db Or https://support.office.com/en-us/article/fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db
Views: 5464 ExcelIsFun
Excel Magic Trick 1500: Lookup All Products For Supplier & Join in Single Cell TEXTJOIN & IF
 
05:54
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1499-1500.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to lookup a supplier name in a lookup table and return all the products in a single cell. This is a Text Formula that is also an Array Formula. This formula uses the IF Function and the TEXTJOIN Function. List all Products in Single Cell. TEXTJOIN Function, Array Formula. Text Formula.
Views: 4361 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: 30013 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: 37832 ExcelIsFun
Excel Magic Trick #90: Consolidation
 
09:46
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: 194990 ExcelIsFun
Excel Magic Trick #19: Randomly Generate Words
 
03:35
Download Excel workbook: 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: 32207 ExcelIsFun
Excel Magic Trick #108: Exchange Rate Table From Web Query
 
10:15
See how to do a Currency Web Query, the CHAR function and concatenation to create an Exchange Rate table. CHAR function DOLLAR function VLOOKUP function Currency Web Queries
Views: 49455 ExcelIsFun
Excel Magic Trick 916: Remove Blanks In Data Validation Drop Down List 5 Examples
 
23:58
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm 3 examples of how to Remove Blanks In Data Validation Drop Down List 5 Examples: 1. Learn that "Ignore Blanks" Check Box does not remove blanks from list, but instead it avoids an error message when cell is empty 2. Remove Blanks from data set using: GoTo (F5), Special Button, Blanks, OK button, Right-Click and Delete Cells, Shift Up 3. Array Formula To Extract Data for Non-Empty Cells using the functions IF, ROWS, COUNTA, INDEX, SMALL, ROW and Comparative Operator NOT 4. Array Formula To Extract Data for Non-Empty Cells using the functions IF, ROWS, COUNTA, INDEX, AGGREGATE, ROW and Comparative Operator NOT 5. Create Dynamic Range Defined Name using INDEX and COUNTIF and the "One Or More Character" Criteria "?*"
Views: 101429 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: 18165 ExcelIsFun
Excel Magic Trick # 3: AVERAGE & Go To Blanks
 
03:30
Download Excel workbook: 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: 89539 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: 37486 ExcelIsFun
Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells
 
07:23
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to do a three way lookup when there are two row header variables (criteria) and one column header (criteria) variable using INDEX and MATCH functions for a normal two way lookup, but with the two row headers joined to create a single row header criteria. In essence, three way lookup is turned into a standard two way lookup. Also see IFERROR function.
Views: 312349 ExcelIsFun
Excel 2010 Magic Trick 672: AGGREGATE function Replaces SMALL Function Array Formula
 
08:10
See how you can use the non-Ctrl+Shift+Enter function AGGREGATE in place of SMALL in an array formula. Link for file: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm
Views: 14289 ExcelIsFun
Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)
 
50:06
Download Excel File #1: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468.xlsm Download Excel File #2: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468Finished.xlsm Download Zipped Folder: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468TextFiles.zip Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Buy excelisfun products: https://teespring.com/stores/excelisfun-store Learn about how Topics: 1. (00:16) Introduction 2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016 3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report! 4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set 5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report. 6. (22:34) Diagram to understand Data Model and Power Pivot 7. (46:46) Unhanded Exception Error. 8. (48:02) Summary Reference Video: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model https://www.youtube.com/watch?v=Jzt-I4mt8sQ
Views: 152982 ExcelIsFun
Excel Magic Trick # 267: Percentage Change Formula & Chart
 
06:41
Download file in "Excel Magic Trick" section: https://people.highline.edu/mgirvin/excelisfun.htm Learn about the universal formula for Percentage Change: (End Value)/Beg Value) - 1 = Percentage Change. The see how to create and format a chart with two data series and two chart types in one chart: Line Chart and Column Chart.
Views: 502749 ExcelIsFun

Writing dissertation service
144 eme newsletter formats
Cover letter for pharmacy intern position
Dal newsletter formats
Good sample of cover letter