Home
Search results “Magic in excel”
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: 29734 Deepak EduWorld
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: 80185 ExcelIsFun
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: 23022 My Live Support
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: 17505 Excel Fx
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: 180485 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: 22789 ExcelIsFun
Excel Magic Trick #184: Setup Database in Excel
 
07:50
See how to create a simple database in Excel using the List or Table feature. A simple database can be created in Excel using the Excel 2003 "List" feature or the Excel 2007 "Table" feature. Fields names must be in first row (no blanks). Records are in rows (no blanks). Other data in the sheet cannot be next to the Table/List (at least one blank row or column between other data and the Table/List Keyboard shortcuts: Excel 2003 List: Ctrl + L. Excel 2007 Table: Ctrl + T. The ranges are dynamic: formulas, pivot tables, charts will all automatically update
Views: 899981 ExcelIsFun
Excel Magic Trick 900: Simulate Filter With Formula to Create Immediate Filter Results
 
16:02
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to create an immediate filter governed from criteria entered into cells that shows all records when no criteria is entered in cells, but filters the data when criteria is entered. Enter Exact or Partial Criteria. Also see how to create Dependent Data Validation List and Dynamic ranges using the table feature. Some of the topics in this video: 1. Defined Names keyboard shortcuts: 2. F3 = Paste Name 3. Ctrl + F3 = Name Manager 4. Ctrl + Shift + F3 = Create Names From Selection 5. Dependent Data Validation List using INDIRECT Formula (Data Validation based on Another Data Validation List) 6. Helper Columns to avoid array formula and speed up calculation time. 7. Helper column formula that shows row number when exact or partial criteria is entered in the criteria area using the functions SUM, AND, OR, ISNUMBER, COUNTA and SEARCH. 8. MAX function to count the number of records that match the criteria 9. Simple INDEX and MATCH function to lookup matching records. 10. Extract Records with Exact & Partial Criteria, But Show All Records When No Criteria Entered 11. Excel Table / List feature to create dynamic ranges 12. Excel Table / List feature to automatically copy formula down column when new records are entered.
Views: 79319 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: 670392 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: 7121 ExcelIsFun
Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records
 
18:02
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import and transform Data Set, including removing Columns, Filter For Records and adding an extra Column to calculate Net Revenue using the Number.Round Power Query Function. 3. (06:38) Load Report to Sheet 4. (07:09) Import each Parameter/Criteria Table. 5. (07:36) Convert each Criteria Table to a single Text Item using the Record.Field Power Query Function. 6. (11:38) Declare and Insert Variables into M Code for Report 7. (14:10) Test Reporting System 8. (14:30) Create Recorded Macro To Refresh All and assign it to a Form button 9. (16:40) Test Macro and Reporting System 10. (17:25) Summary
Views: 71275 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: 92582 ExcelIsFun
The Magic of Excel Keyboard Shortcuts
 
06:21
The Magic of Excel Keyboard Shortcuts... Excel keyboard shortcuts generally start with by pressing the "Alt" key. So, once you press the "Alt" key, you will see a shortcut or in fact an alphabet for each tab in the ribbon. Like F is for File, H is for Home, N for insert and so on. In the old excel interface, I mean 2003, you can find an underline under the letter in the menu which denotes the short key. More Amazing excel keyboard shortcuts can be seen in the following playlist: https://www.youtube.com/watch?v=4gVTZW7yOfU&list=PLRJY-_DDvSgIuJQgKLwIqrUESnH_cQxqW When you press the short key, the ribbon opens up and all the commands in the ribbon appear with the next level of short key. If you use some commands very prominently, I would recommend that do not use the mouse to navigate to the command but use the short key. It will make a lot of difference in the way you work in excel and the efficiency as well. Friends once you start working on excel day in and day out you will see how easier these short keys become for you to work on the commands. Share your experiences with me in the comments section below Now coming to the worksheet, there are some important and easy shortcuts that you should remember. I will cover some of them while you can keep updating the important ones in the comments section. You can use the arrow keys to move to the immediate left, right, top and bottom cells to the one which active. Pressing the home key will take you to the first cell of the current row. But when you press Ctrl+Home it takes you to the first cell of the worksheet A1 Page up will move the cell one full screen up in the same column and page down will move one full screen down. Ctrl + Page up moves you to the next tab of the worksheet and ctrl+page down moves to the previous tab of the worksheet. One short key I commonly use is the ctrl+ the arrow keys. What is does is that it moves to the first cell in that direction of arrow which is followed or preceded by a blank cell. Say for example you have data arranged something like this and when you are in the say column D, and you are moving down the column using the arrow key, when you reach this cell D7 either you have to press the down arrow 4 times to reach D11 which has the next data entry or use your mouse or press Ctrl+Down arrow. Simple! Here there is a dataset which has just a difference of 4 cells between cells with data. What if the difference is 100 or 500 cells; using your mouse or scrolling the worksheet; no that for sure is counterproductive. These were some of the scrolling shortkeys, here are a few more that will surely help you during work on excel. Now how many times have we used the Ctrl+c and Ctrl+v keys for copy and paste. There is a faster way to paste; which is simply press the enter key!! So, if I copy data from say this cell by using the Ctrl+C combination, now wherever you want to paste it; just go over there and hit enter. You can even press the same data in a range like this. Or you may replicate a range as well by using the enter key You can also move to a particular cell by typing the cell address in the name box like this. And then simply if you want to close a worksheet press Ctrl+W and to quit the application Alt+f4 Welcome to Excel is Simple. A place where excel is taught in the dumbest possible way. You will find every type of excel tutorial; excel 2013 tutorial for beginners, excel 2010 tutorial for beginners, excel formulas and functions, excel pivot table tutorial, excel vlookup. Aptly this is one place which has all excel tutorial for beginners. Subscribe to my channel at http://www.youtube.com/user/excelissimple?sub_confirmation=1 Want some focused playlists. Click here and lean back: The IF formula explained: https://www.youtube.com/playlist?list=PLRJY-_DDvSgK7khaQJLqhry-PELQ0hLTe All excel formulas in one place: https://www.youtube.com/playlist?list=PLRJY-_DDvSgKsM7G8qQEAa3oWLDCUn401 Excel basics if you have just started: https://www.youtube.com/playlist?list=PLRJY-_DDvSgLUOnfRBwVQYZVVzOxaVz76 Stay connected at: Blog: http://excelissimple.blogspot.com/ Facebook: https://web.facebook.com/excelissimple Wonder How to: http://creator.wonderhowto.com/akhilkakroo/ Instructables: http://www.instructables.com/id/Excel-Teacher/ Twitter: https://twitter.com/akhilkakroo Pinterest: https://www.pinterest.com/akhilkakroo/excel/ Google Plus: google.com/+excelissimplenow Share, like and post any questions on the videos #Excel #MSExcel #Microsoft #excel #excelfunctions #exceltutorial
Views: 250750 Excel Is Simple
Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates
 
17:34
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm Concepts: 1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers. 2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE). In this Video: 1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges. 2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges. 3. How Come VLOOKUP will not work 4. Find Hurdle Value for Top 5 using LARGE function 5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)
Views: 129776 ExcelIsFun
Excel Magic Trick #187: Unique List w Dynamic Formula
 
09:41
Create a unique list of items for a large list that contains duplicates with 2 array formula that contain the functions: SUM, IF, ROWS, COUNTIF, INDEX, SMALL, and ROW Related Videos: Excel Magic Trick #180: COUNTIF function formula 21 Examples 05 Excel Series: Array Formulas 1-14 Various INDEX Lookup w TRUE/FALSE criteria "Reverse Lookup" Excel Magic Trick 185 Dynamic Formula Extract Data 1 criteria Excel Magic Trick 186 Dynamic Formula Extract Data 2 criteria Excel Magic Trick # 156: List Of Unique Items w Formula From 2 Lists Excel Magic Trick 82: Advanced Filter Extract Unique Records
Views: 56435 ExcelIsFun
Excel Magic Trick #22: NORMDIST function for Probability
 
07:36
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 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: 82389 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: 144208 ExcelIsFun
एक कोड जो आपको बना देगा स्मार्ट | Excel Interesting Topic | Excel Magic
 
03:08
इस वीडियो में मैंने एक बताया है जो जो आपको बना देगा स्मार्ट वीडियो ३ मिनट का ही है देखलो फिर लिखे बटन पे भी क्लिक करना ना भूलो मेरे और भी वीडियोस है वो भी देखलो : एक्सेल अमेजिंग फीचर्स देखने के लिए - https://youtu.be/6ZxGvtzKymw एक्सेल बेसिक से एडवांस - https://youtu.be/iV1jMcsy9qM एक्सेल VBA सीरीज - https://youtu.be/DNTlzpnOQ6A एक्सेल MIS वीडियोस - https://youtu.be/_R1VyDCSoUk टेक्निकल वीडियोस - https://youtu.be/NghHXwSYScs हार्डवेयर हिंदी में सीखने में लिए - https://youtu.be/tKcHNZsuHJE
Views: 40013 Learn More
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: 520036 ExcelIsFun
Excel Magic - कैसे 10,000 PaySlip केवल 2 मिनट में बनाये || Make Thousands Payslips just in 2 minutes
 
13:26
how to make payslip in excel - Excel Magic - कैसे 10,000 PaySlip केवल 2 मिनट में बनाये || Make Thousands Payslips just in 2 minutes - yes friends we can easily create/generate/make thousands of payslips just within few minuts only. here in this video DEEPAK EDUWORLD will show you. To do this task we have to use the mailing feature of ms word also. you can do this in any of excel version 2007 2010 2013 & 2016. salary slip/salary sheet/payroll sheet/advanced excel/hindi Download Excel Training File https://drive.google.com/open?id=1uomGmbumCSTI_IMCHZjQrVwEsIM3Y7bX Download MS Word Training FIle https://drive.google.com/open?id=1D1VeIQe54DjnA2xCUS8EMR0LM58YFpZP MS Word Insert Menu In Hindi | How to Insert Table | Complete Design & Layout Tab of table in detail https://youtu.be/uLSH5vgvwnM 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 DEEPAK NEHRA ***********************************************************
Views: 90019 Deepak EduWorld
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: 1091167 Excel to Excel
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: 293953 ExcelIsFun
How to create a 4-Quadrant Matrix Chart in Excel
 
10:49
Topic: How to create a 4-Quadrant Matrix Chart in Excel Business Scenario: You need to show executive compensation by Salary value and by Compa-ratio in a Matrix chart Formula: Scatter Chart
Views: 35244 Caripros HR Analytics
Create an Excel Database in Minutes -- Magic Userform Code
 
06:16
http://www.onlinepclearning.com Create an Excel Database in Minutes -- Magic Userform Code This little bit of code will transfer the data from the Userform to the worksheet into a flat file database. It will work with 1 control to 1000 controls on the form. This little bit of generic code that I have written is going to save heaps of headaches. I have used this in larger applications but now I have cut it to the bear and basic essentials.
Views: 87223 Online PC Learning
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: 47597 ExcelIsFun
Excel Magic Trick 1357: Power Query Function to Repeat Clean & Transform Steps for Many Excel Files
 
18:43
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Goal: Unpivot and Clean Cross Tabulated Tables in multiple Excel Workbook Files and Create a Single Proper Data Set. Create a Power Query Function to repeatedly perform the Clean & Transform Task across many Excel Files. Lean how to use the new Invoke Custom Function option for a Custom Column. Learn many powerful features in Power Query (Get & Transform): 1. (00:16) Introduction: Overview of whole process, including looking at the files that we need to import, clean, transform and consolidate 2. (02:00) Build Custom Power Query Function with the steps listed below (3 – 12) 3. (03:47) Transpose Table (to deal with the fact that there are two column headers with conditions that need to be Unpivoted) 4. (04:30) Promote Headers 5. (04:48) UnPivot Other Columns based on the two columns Date and Fair 6. (05:40) Rename Columns 7. (06:05) Close and Load to “Create Connection Only” 8. (06:34) Duplicate Query 9. (06:52) Look at M Code and how it is set up and automatically written when you use the User Interface in Power Query 10. (08:27) Add lines of M Code to convert duplicated query to a Custom Power Query Function 11. (09:42) Import Files From Folder: Import Multiple Excel Files with Cross Tabulated Tables that need to be cleaned and Unpivoted. 12. (10:34) Transform extension column to lowercase letters and then Filter for only Excel Files with extension “.xlsx” 13. (11:25) Add Custom Column with Excel.Workbook Function to get Excel Objects, such as Sheets. 14. (12:58) Expand Custom Column to show objects, and to expose sheets with the Cross Tabulated Tables. 15. (13:37) Filter to import only Sheet Objects. 16. (14:09) Filter Out Sheet Tabs that contain the word “Sheet” (Sheet Tabs that do not have a Sales Rep Name. 17. (14:30) Remove Other Columns (Not Data or Name) 18. (14:42) Invoke Custom Power Query Function 19. (14:34) Remove Data Column 20. (14:39) Expand Columns 21. (15:55) Set Data Types and rename columns 22. (16:34) Load to Excel Worksheet (Table, Only Create Connection or Data Model) 23. (17:36) Test Updating by dropping new files in our folder 24. (18:20) Summary
Views: 39616 ExcelIsFun
Excel Magic Trick 1271: Extract Top 20 Values & Dates: Formula, Array Formula or Power Query?
 
17:18
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to extract Top 20 values and dates for those values using Formulas when there are no duplicates, Array Formula for when there are duplicates and Power Query: (00:01) Microsoft Data Insights Summit :http://datainsightssummit.com/ (00:17) Introduction to video topics 1. (01:43) Formula Method when there are no duplicates. See the functions: LARGE, INDEX and MATCH 2. (06:18) Array Formula for when there are duplicate values. See the functions: LARGE, INDEX, AGGREGATE, ROW and COUNTIFS. 3. (13:48) Power Query Method Microsoft Data Insights Summit: http://datainsightssummit.com/
Views: 18746 ExcelIsFun
Excel Dynamic Arrays & Conditional Formatting for Spilled Arrays (Excel Magic Trick 1525)
 
05:14
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1524-1525.xlsx In this video learn about Spilled Dynamic Arrays & Conditional Formatting. Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Views: 3461 ExcelIsFun
Excel Magic Trick 1347: Power Query Function: Split Master Table into Sub Tables for Each Product
 
21:05
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to create a Power Query (Get & Transform) Custom Function with a parameter in order to create five Sub Tables, one for each product, each on a new sheet, from a Master Table: 1. (00:17) Introduction. See finished Power Query Function to Extract Records to A Sheet Based on Criteria / Condition / Parameter. 2. (02:23) Import Excel Master Table into Query Editor. 3. (03:19) Filter Records and Add Custom Column to Calculate Revenue. See the Function Number.Round. 4. (07:10) Look at M Code and learn about some of the conventions for M Code. 5. (09:57) Load Query to Sheet to Test where the M Code works. 6. (10:29) Build Power Query Custom Function. Create Parameter / Input Variable / Changeable Criteria in M Code. 7. (12:58) Invoke Function to Create Sub Tables 8. (14:32) Test whether the sub tables 9. (15:57) Learn how to edit the Parameterized Custom Function so that all five invoked functions and the five Sub Tables update perfectly! Learn how to use the Table.RemoveColumns function. 10. (20:36) Summary Power Query (Get & Transform) Custom Function Power Query (Get & Transform) Code with an Input Variable Power Query (Get & Transform) with Input Criteria Power Query (Get & Transform) Parameterized Custom Function Build Re-Usable M Code in Power Query (Get & Transform) Create Power Query (Get & Transform) Function
Views: 15951 ExcelIsFun
Excel 2010 Magic Trick 798: Partial Text Lookup Formula To Return Multiple E-mail Records
 
10:29
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm Rotate Table: 1. Type "man" and return all e-mails and phone numbers with last names that start with "man" 2. See the new 2010 Function AGGREGATE 3. See the functions SEARCH, INDEX, ROW, ROWS, IF and ISNUMBER Related videos: Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records
Views: 24011 ExcelIsFun
Excel Magic Trick 783: Date Functions & Formulas (17 Examples)
 
19:06
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. DAY function 2. TEXT Function to get Day spelled out, like "Monday" 3. TEXT Function to get Month spelled out, like "January" 4. MONTH Function 5. YEAR function 6. Last Day in Month: EOMONTH function 7. Last Day in Month: DATE, YEAR, DAY, MONTH functions 8. First Day Next Month: EOMONTH function 9. First Day Next Month: DATE, YEAR, DAY, MONTH Functions 10. Vest Date: EDATE function 11. List of First Day in Month: EOMONTH 12. WORKDAY function -- find workday in future given a certain number of days. 13. WEEKDAY function 14. Last Monday: DATE, YEAR, MONTH, DAY and WEEKDAY functions 15. Formula for Days Between Two Dates 16. Formula for Days Between Two Dates, Inclusive 17. Net Working Days with NETWORKINGDAYS Function Buy excelisfun products: https://teespring.com/stores/excelisfun-store
Views: 850636 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: 5698 ExcelIsFun
Excel Magic Trick 1024: SUMIF & SUMIFS function & Mixed Cell References & Dynamic Table Range
 
10:26
Download workbook example from video: http://people.highline.edu/mgirvin/excelisfun.htm Add with one or more conditions: 1. Learn how to add with one or more conditions using the SUMIF and SUMIFS functions. 2. Excel table feature to create dynamic ranges, ranges the expand as new records are added. 3. Learn how to use the Excel Table feature with or without the Table Formula Nomenclature. 4. See how to use regular cell references rather than Table Formula Nomenclature so that you can copy the formula to the side and not have the Table Formula Nomenclature move to different columns. 5. Learn how to create a cross tabulated table to add with two conditions 6. Learn how to use Mixed Cell References to help speed up formula creation time.
Views: 118403 ExcelIsFun
Excel 2013 Flash Fill : 23 Amazing Examples (Preview #7) (Excel Magic Trick 955)
 
09:17
Download: http://people.highline.edu/mgirvin/ExcelIsFun.htm The Amazing Flash Fill Excel 2013 Feature will Extract, Concatenate (Join), Insert or Reverse Data (Text, Dates, Numbers, Time) See how to: 1. Flash Fill Keyboard: Ctrl + E to Extract First and Last names (Remember: Flash Fill does not recognize changes in source data, but formulas do.) 2. Extract First Letter of First or First Letter of Last name when data is separated by space. 3. Extract Initials 4. Extract First 2 characters of First 5. Extract First 3 characters of First 6. Extract First 4 characters of First 7. Extract First 5 characters of First 8. Extract First 6 characters of First?? - Actually, it tells Excel: First Name and Initial of Last 9. Extract with Dots 10. Extract with @ symbol 11. Join or Concatenate Text 12. Extract Year, Month or Day from Date 13. Extract Pennies from Sales Number 14. Extract Integer from Sales Number 15. Extract Dollars from Sales Number 16. Concatenate Date and Dollars 17. Insert Comma 18. Insert Text 19. Extract Hours from Time 20. Extract Minutes from Time? 21. Extract and Insert with Text: Get First Initial with period and Last Name 22. Reverse Last and First Name 23. Reverse Last and First Name and Insert Comma
Views: 54939 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: 57821 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: 43565 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: 104634 ExcelIsFun
Excel Magic Trick #5: Data Validation List & VLOOKUP
 
04:16
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 name a cell range, use data validation to add a drop-down list, and how to use the VLOOKUP function to look up values. See how to do this lightening fast with keyboard short cuts! Excel Magic Trick 167p1 IF function formula: 12 Examples! Excel Magic Trick 167p2 IF function formula: 12 Examples!
Views: 283136 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: 287305 ExcelIsFun
Excel Magic Trick 1155: IF Function: Checking For Empty Cells (5 Methods)
 
06:47
Download File: http://people.highline.edu/mgirvin/excelisfun.htm IF Function: Checking For Empty Cell & Zero Length Text Strings: 1. (00:08 minute mark) Problem Setup 2. (00:56 minute mark) IF function logical test using Zero Length Text String (checks for empty cell or zero length text string) 3. (01:40 minute mark) IF function logical test using ISBLANK function (checks for empty cell) 4. (03:10 minute mark) IF function logical test using ISNUMBER function (checks for number) 5. (04:04 minute mark) IF function logical test using OR function to check two cells using Zero Length Text String (checks for empty cell or zero length text string) 6. ( minute mark) IF function logical test using COUNT function to check two cells to see if they are numbers (COUNT counts numbers ONLY) Formula for Number of Days between two dates, How to avoid the empty cells Check for empty cells, Check for Blanks. Check for Zero Length Text String
Views: 65945 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: 16807 ExcelIsFun
Magic 🎩 Of Autocorrect in Word & Excel in Hindi Typing
 
05:49
दोस्‍तों इस Video में हमने बताया है कि कैसे आप Word & Excel में Hindi या English के लम्‍बे लम्‍बे नाम बार बार Type करने से बच सकते हैं autocorrect options का इस्‍तेमाल करके तो आईये जानते हैं Magic Of Autocorrect in Word & Excel in Hindi Fix Hindi letters that changes automatically 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] पर मेल कर सकते हैै
Views: 36523 mybigguide
Excel Magic Trick 381: Merge Two Lists Tables w Advanced Filter and Remove Duplicates Feature
 
04:27
See how to merge two lists into one list while keeping unique items from List 1 and list 2 and removing duplicates. See an Advanced Filter Method and a Remove Duplicates method.
Views: 85505 ExcelIsFun
3 Magic Words for Excel Macro | Vba Code to - Google Search
 
08:01
3 Magic Words for Excel Macro | Vba Code to - Google Search http://courses.yodalearning.com/p/free-office-2016-tips It will teach you to code without coding knowledge with 3 Magic words. It's in continuation of series for automating your work with Excel VBA for Office 2007 & version thereafter. [Unlock the surprise by clicking on (i) icon on Top Right of video] This Excel VBA Tutorial file shown in the video is available for FREE download at http://yodalearning.com Enroll our FREE Course at http://courses.yodalearning.com/p/free-office-2016-tips Keep yourself updated. Follow us now! Like us on http://www.facebook.com/yodalearning Tweet us on http://www.twitter.com/yodalearning Follow our boards at https://in.pinterest.com/yodalearning/pins/
Views: 8212 Excel Yoda learning
Excel Magic Trick 923: Recorded Macro Basics: Click Button To Move Data To Table On Another Sheet
 
13:34
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to: 1. Enter Data On One Sheet and then click Button To Send Data To Table on Second Sheet 2. Recorded Macro: Move Record From Data Entry Sheet To Table On Second Sheet 3. Learn how to Show Developer Ribbon (Excel 2010 = Right-click Ribbon, Customize Ribbon, Checkbox for Developer Ribbon) 4. Keyboard: Alt + F8 = List of Macros 5. Keyboard: Alt + F11 = VBA Editor 6. Learn how to use Absolute and Relative References in Recorded Macro 7. Add Form Button To Sheet that can be clicked to execute recorded Macro. 8. Absolute References = Selecting Cells with Macro Recorder on means Absolute Movement 9. Relative References = Selecting Cells with Macro Recorder on means Relative Movement 10. View Code: Range function for Absolute References, OFFSET Function for Relative References
Views: 351284 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: 318777 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: 28715 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: 654440 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: 24920 ExcelIsFun
Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number
 
05:49
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1501-1502.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.
Views: 5903 ExcelIsFun