title
Top 20 Microsoft Excel Tips & Tricks

description
In this step-by-step tutorial, learn the top 20 best Microsoft Excel tips and tricks. 👋 Resources called out in this video: - Excel sheet that I used if you want to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whKMxYi3HuSk1aLx-PA?e=BIFDPp - In-depth Pivot Table tutorial: https://youtu.be/m0wI61ahfLc - Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com ⌚ Timestamps 0:00 Introduction 0:17 Convert image to table in Excel using phone 1:29 Use Excel for free on the web 2:08 Use Ideas to generate insights on your data 3:19 Define names for cells 4:42 Absolute & relative cell references 6:35 Macros 8:35 Data types 9:38 Xlookup function 11:47 Sum function 12:43 Concatenate and flash fill 13:48 Sumif function 14:45 Countif function 15:35 IF function 16:48 Calculate difference between dates & convert function 18:11 Adjust column width & row height automatically 18:56 Freeze panes 19:33 Pivot Tables 21:14 Drop down lists 22:16 Import data from web 23:54 Collaborate with others & comment 24:45 Wrap up 📃 Watch related playlists and videos - Excel: https://www.youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY 🚩 Connect with me on social: - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 🔔 Subscribe to my YouTube channel https://www.youtube.com/user/kevlers?sub_confirmation=1 🎬 Want to watch again? Navigate back to my YouTube channel quickly http://www.kevinstratvert.com

detail
{'title': 'Top 20 Microsoft Excel Tips & Tricks', 'heatmap': [{'end': 775.405, 'start': 721.318, 'weight': 0.853}, {'end': 835.462, 'start': 814.445, 'weight': 0.743}], 'summary': 'Covers 20 essential excel tips including photo to data conversion, web access, cell naming, macros; automating tasks with macros; new data types, xlookup function; excel functions like xlookup, sum, concatenation, sumif, countif, if; adjusting column widths, row heights, freezing panes, pivot tables; data visualization with profit data for us and china, and tips on pivot tables, pivot charts, web data import.', 'chapters': [{'end': 402.564, 'segs': [{'end': 39.753, 'src': 'embed', 'start': 17.54, 'weight': 0, 'content': [{'end': 26.165, 'text': 'Tip number one, you can take a photo with your iPhone or your Android phone of data and then you could convert that into Microsoft Excel.', 'start': 17.54, 'duration': 8.625}, {'end': 34.63, 'text': 'To be able to do this, click into the App Store on an iPhone or the Play Store on an Android phone and download an app called Microsoft Office.', 'start': 26.545, 'duration': 8.085}, {'end': 38.933, 'text': "Once you download the app, you'll have an icon on your phone with the Office logo.", 'start': 35.151, 'duration': 3.782}, {'end': 39.753, 'text': "Let's click on that.", 'start': 38.993, 'duration': 0.76}], 'summary': 'Convert data photos to excel using microsoft office app on iphone or android.', 'duration': 22.213, 'max_score': 17.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y17540.jpg'}, {'end': 134.698, 'src': 'embed', 'start': 104.517, 'weight': 1, 'content': [{'end': 109.602, 'text': "once you sign in to office.com, over on the left hand side there's an icon for excel.", 'start': 104.517, 'duration': 5.085}, {'end': 110.363, 'text': "let's click on this.", 'start': 109.602, 'duration': 0.761}, {'end': 116.186, 'text': 'This opens up the Excel start page and here we can click into a new blank workbook.', 'start': 110.783, 'duration': 5.403}, {'end': 118.567, 'text': 'Alternatively, you could also start from a template.', 'start': 116.266, 'duration': 2.301}, {'end': 127.932, 'text': 'This drops us into a new Microsoft Excel worksheet and we have the most commonly used controls directly here in Excel on the web.', 'start': 118.867, 'duration': 9.065}, {'end': 134.698, 'text': 'Tip number three, you can use ideas in Microsoft Excel to get interesting insights on your data.', 'start': 128.151, 'duration': 6.547}], 'summary': 'Access excel on office.com, start new workbook, use ideas for data insights.', 'duration': 30.181, 'max_score': 104.517, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y104517.jpg'}, {'end': 172.846, 'src': 'embed', 'start': 149.79, 'weight': 2, 'content': [{'end': 158.716, 'text': "This opens up a pane on the right-hand side and you can see that Microsoft Excel has analyzed the data and it's created all these interesting views that look at the data.", 'start': 149.79, 'duration': 8.926}, {'end': 163.259, 'text': 'For instance, here I see that the United States accounts for the majority of sales.', 'start': 159.096, 'duration': 4.163}, {'end': 166.762, 'text': 'If I want to keep this insight, I could insert it into my sheet.', 'start': 163.7, 'duration': 3.062}, {'end': 170.764, 'text': "Up above, what's interesting as well is I can ask a question about my data.", 'start': 167.042, 'duration': 3.722}, {'end': 172.846, 'text': 'Here I can see some example questions.', 'start': 171.125, 'duration': 1.721}], 'summary': 'Microsoft excel analyzes data, revealing us as majority sales contributor. users can insert insights into their sheet and ask questions about the data.', 'duration': 23.056, 'max_score': 149.79, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y149790.jpg'}, {'end': 259.077, 'src': 'embed', 'start': 230.234, 'weight': 3, 'content': [{'end': 235.521, 'text': "To do this, let's highlight all of the revenue numbers and then, if we click on the formulas tab,", 'start': 230.234, 'duration': 5.287}, {'end': 239.605, 'text': "it'll open up the formulas ribbon and in the middle we can define a name.", 'start': 235.521, 'duration': 4.084}, {'end': 241.207, 'text': "Let's click on define name.", 'start': 239.926, 'duration': 1.281}, {'end': 244.551, 'text': 'Here it automatically detects that the column header is revenue.', 'start': 241.668, 'duration': 2.883}, {'end': 245.673, 'text': "Let's go with that.", 'start': 244.872, 'duration': 0.801}, {'end': 250.833, 'text': "Next, I can also highlight my cost column and I'll define a name for this as well.", 'start': 246.391, 'duration': 4.442}, {'end': 254.215, 'text': "Here it selects the name of the header and let's click on OK.", 'start': 251.194, 'duration': 3.021}, {'end': 259.077, 'text': 'Now, if I go back to my profit column, instead of entering in the sell values,', 'start': 254.856, 'duration': 4.221}], 'summary': 'Demonstrating defining names for revenue and cost columns in a spreadsheet.', 'duration': 28.843, 'max_score': 230.234, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y230234.jpg'}, {'end': 402.564, 'src': 'embed', 'start': 361.082, 'weight': 4, 'content': [{'end': 363.824, 'text': 'and it adds a dollar sign in front of the letter and the number.', 'start': 361.082, 'duration': 2.742}, {'end': 366.105, 'text': 'It is now an absolute cell reference.', 'start': 364.004, 'duration': 2.101}, {'end': 373.508, 'text': "When I hit enter and I paste this down, if I go to the last cell now, you'll see that it's locked it to this 10%.", 'start': 366.485, 'duration': 7.023}, {'end': 379.151, 'text': 'This is an absolute reference, and using the F4 key makes it really easy to set this in place.', 'start': 373.508, 'duration': 5.643}, {'end': 388.195, 'text': "What you can do as well, when I go up to L1 over here, if I press F4 again, here it'll toggle it so only the row is locked.", 'start': 380.011, 'duration': 8.184}, {'end': 390.236, 'text': 'If I press it again, only the column is locked.', 'start': 388.235, 'duration': 2.001}, {'end': 395.419, 'text': "And if I press it again, I'm back to the initial formula, which is a complete relative reference.", 'start': 390.336, 'duration': 5.083}, {'end': 401.123, 'text': 'Tip number six, I want to show you how you can record and use macros like a pro.', 'start': 395.959, 'duration': 5.164}, {'end': 402.564, 'text': 'What are macros??', 'start': 401.523, 'duration': 1.041}], 'summary': 'Using f4 key to create absolute cell references and how to record and use macros efficiently.', 'duration': 41.482, 'max_score': 361.082, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y361082.jpg'}], 'start': 0.029, 'title': 'Excel tips & tricks', 'summary': 'Covers 20 essential tips and tricks in microsoft excel, including photo to excel data conversion, free web access, and using ideas for data insights. it also explains optimizing excel usage via features like defining cell names, mastering cell references, and using macros, with a focus on user-friendly formulas and functions.', 'chapters': [{'end': 170.764, 'start': 0.029, 'title': 'Excel tips & tricks: top 20 must-knows', 'summary': 'Covers 20 essential tips and tricks in microsoft excel, including how to convert a photo into excel data, accessing excel for free on the web, and using ideas in excel for data insights.', 'duration': 170.735, 'highlights': ['You can take a photo with your iPhone or Android phone and convert it into Microsoft Excel using the Microsoft Office app, allowing you to select and import specific data, such as dates, impressions, and clicks. The Microsoft Office app allows users to convert photos of data into Microsoft Excel, enabling the selection and import of specific data, such as dates, impressions, and clicks.', 'Excel can be accessed for free on the web via office.com, with the option to start from a new blank workbook or template, providing commonly used controls directly in Excel on the web. Users can access Excel for free on the web through office.com, offering the option to start from a new blank workbook or template and providing commonly used controls directly in Excel on the web.', "Using ideas in Microsoft Excel allows for automatic analysis of data and the generation of insightful views, such as identifying the majority of sales coming from the United States, and the ability to ask questions about the data. Microsoft Excel's 'ideas' feature automatically analyzes data and generates insightful views, such as identifying the majority of sales coming from the United States, and offers the ability to ask questions about the data."]}, {'end': 402.564, 'start': 171.125, 'title': 'Excel tips and tricks', 'summary': 'Explains how to optimize excel usage by using features such as defining cell names, mastering absolute and relative cell references, and recording and using macros, with a focus on providing a user-friendly and understandable approach to formulas and functions.', 'duration': 231.439, 'highlights': ['The chapter explains the importance of defining cell names for friendlier and more understandable formulas, demonstrating how to define names for revenue and cost columns, resulting in a much friendlier formula and easier understanding. Improved formula readability and understanding.', 'The section illustrates the significance of mastering absolute and relative cell references, showcasing the use of the F4 key to toggle between absolute and relative references, ultimately simplifying the process of setting these references in place. Simplified process of setting absolute and relative references.', 'The chapter introduces the concept of recording and using macros, providing an introduction to this feature and setting the stage for further exploration of its potential applications. Introduction to the concept of recording and using macros.']}], 'duration': 402.535, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y29.jpg', 'highlights': ['You can convert a photo into Microsoft Excel using the Microsoft Office app, allowing import of specific data like dates, impressions, and clicks.', 'Excel can be accessed for free on the web via office.com, offering commonly used controls directly in Excel on the web.', 'Using ideas in Microsoft Excel allows for automatic analysis of data and generation of insightful views, such as identifying the majority of sales coming from the United States.', 'The chapter emphasizes the importance of defining cell names for friendlier and more understandable formulas.', 'It illustrates the significance of mastering absolute and relative cell references, simplifying the process of setting these references in place.', 'The chapter introduces the concept of recording and using macros, setting the stage for further exploration of its potential applications.']}, {'end': 528.167, 'segs': [{'end': 528.167, 'src': 'embed', 'start': 486.055, 'weight': 0, 'content': [{'end': 489.177, 'text': 'Basically, this is Excel remembering all the steps that you run through.', 'start': 486.055, 'duration': 3.122}, {'end': 491.078, 'text': "Now that I've recorded all the steps,", 'start': 489.437, 'duration': 1.641}, {'end': 497.882, 'text': 'I want to add an additional country and instead of going through and manually copying and pasting the country name and then all the cookie types,', 'start': 491.078, 'duration': 6.804}, {'end': 499.143, 'text': "I'm going to run my macro.", 'start': 497.882, 'duration': 1.261}, {'end': 504.147, 'text': "Once again, let's go up to macros, click on view macros, and I can now run my macro.", 'start': 499.604, 'duration': 4.543}, {'end': 505.748, 'text': 'check that out.', 'start': 504.787, 'duration': 0.961}, {'end': 509.331, 'text': 'it has now automated all of those manual steps that i took.', 'start': 505.748, 'duration': 3.583}, {'end': 512.033, 'text': 'this is just a simple example of how you can use macros.', 'start': 509.331, 'duration': 2.702}, {'end': 515.936, 'text': 'you could also use it for far more complex examples.', 'start': 512.033, 'duration': 3.903}, {'end': 521.461, 'text': 'tip number seven i want to show you how you can use data types in microsoft excel.', 'start': 515.936, 'duration': 5.525}, {'end': 523.842, 'text': 'to use data types up on the top ribbon.', 'start': 521.461, 'duration': 2.381}, {'end': 528.167, 'text': "let's go over and click on data and right in the middle there are data types.", 'start': 523.842, 'duration': 4.325}], 'summary': 'Excel macro automates manual steps, data types used in excel.', 'duration': 42.112, 'max_score': 486.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y486055.jpg'}], 'start': 402.624, 'title': 'Automating tasks with macros in excel', 'summary': 'Demonstrates the usage of macros in excel to automate repetitive tasks, reducing manual steps, and increasing efficiency. it showcases an example of creating a macro to add additional countries and automate the process of copying and pasting cookie types.', 'chapters': [{'end': 528.167, 'start': 402.624, 'title': 'Automating tasks with macros in excel', 'summary': 'Demonstrates how to use macros in excel to automate repetitive tasks, such as copying and pasting data, and showcases an example of creating a macro to add additional countries and automate the process of copying and pasting cookie types, reducing manual steps and increasing efficiency.', 'duration': 125.543, 'highlights': ['The chapter demonstrates how to use macros in Excel to automate repetitive tasks The transcript shows how macros can be used in Excel to automate repetitive tasks, such as copying and pasting data, saving time and reducing manual steps.', 'Creating a macro to add additional countries and automate the process of copying and pasting cookie types An example is provided where a macro is created to add additional countries and automate the process of copying and pasting cookie types, showcasing the efficiency and time-saving benefits of using macros in Excel.', 'Reducing manual steps and increasing efficiency The use of macros in the example results in the reduction of manual steps and an increase in efficiency, demonstrating the practical benefits of automating tasks in Excel.']}], 'duration': 125.543, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y402624.jpg', 'highlights': ['The chapter demonstrates how to use macros in Excel to automate repetitive tasks, saving time and reducing manual steps.', 'An example is provided where a macro is created to add additional countries and automate the process of copying and pasting cookie types, showcasing the efficiency and time-saving benefits of using macros in Excel.', 'The use of macros in the example results in the reduction of manual steps and an increase in efficiency, demonstrating the practical benefits of automating tasks in Excel.']}, {'end': 695.653, 'segs': [{'end': 557.743, 'src': 'embed', 'start': 528.567, 'weight': 0, 'content': [{'end': 530.068, 'text': 'Currently, Excel has two.', 'start': 528.567, 'duration': 1.501}, {'end': 535.531, 'text': 'There are stocks and geography, but coming soon, there will be far more data types available.', 'start': 530.208, 'duration': 5.323}, {'end': 537.291, 'text': 'So what can you do with the data type??', 'start': 535.871, 'duration': 1.42}, {'end': 544.915, 'text': 'Well, here down below, I have three countries or geographies, and I could pull in all types of data related to these.', 'start': 537.572, 'duration': 7.343}, {'end': 548.097, 'text': "So here, for instance, I'm going to highlight these three items.", 'start': 545.255, 'duration': 2.842}, {'end': 551.118, 'text': "I'm going to go up to data types and click on geography.", 'start': 548.517, 'duration': 2.601}, {'end': 557.743, 'text': "It's now converted these to a geography data type and you'll see this icon appears next to it.", 'start': 552.339, 'duration': 5.404}], 'summary': 'Excel currently has two data types - stocks and geography - with more data types coming soon, allowing users to pull in various data related to different countries or geographies.', 'duration': 29.176, 'max_score': 528.567, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y528567.jpg'}, {'end': 620.019, 'src': 'embed', 'start': 589.924, 'weight': 1, 'content': [{'end': 594.626, 'text': 'So what does XLOOKUP do? Well, just like the name implies, you can look up data.', 'start': 589.924, 'duration': 4.702}, {'end': 600.649, 'text': 'Here I have a table with a bunch of data related to different countries and different cookie sales.', 'start': 595.067, 'duration': 5.582}, {'end': 604.671, 'text': 'I want to list out the store manager based on the country.', 'start': 601.209, 'duration': 3.462}, {'end': 608.293, 'text': 'I have another table down below that has the country and the manager.', 'start': 605.171, 'duration': 3.122}, {'end': 613.515, 'text': 'I want to pull the manager from here and insert it up here and XLOOKUP will help with this.', 'start': 608.653, 'duration': 4.862}, {'end': 620.019, 'text': "To use XLOOKUP, let's type in equals and first off type in XLOOKUP and then open parentheses.", 'start': 613.975, 'duration': 6.044}], 'summary': 'Xlookup helps to find and retrieve data efficiently from tables.', 'duration': 30.095, 'max_score': 589.924, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y589924.jpg'}], 'start': 528.567, 'title': 'New excel data types and xlookup function', 'summary': 'Discusses the new data types in excel and demonstrates the conversion of geographic data types. it also covers the use of xlookup function to retrieve specific data, such as store manager based on country, with examples and step-by-step guidance.', 'chapters': [{'end': 695.653, 'start': 528.567, 'title': 'New excel data types and xlookup function', 'summary': 'Discusses the new data types available in excel, showcasing the conversion of geographic data types and the use of xlookup function to look up and retrieve specific data, including the store manager based on the country and related information, with examples and step-by-step guidance.', 'duration': 167.086, 'highlights': ["Excel's new data types include geography and upcoming additions, allowing easy access to various related information such as population and unemployment rate, demonstrated with practical examples. Excel now offers new data types like geography, with upcoming additions, enabling quick access to related information such as population and unemployment rate.", 'Demonstration of using XLOOKUP function to look up and retrieve specific data, including the store manager based on the country, with step-by-step guidance and practical examples. The XLOOKUP function is demonstrated to efficiently look up and retrieve specific data, such as the store manager based on the country, with practical examples and step-by-step guidance.']}], 'duration': 167.086, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y528567.jpg', 'highlights': ["Excel's new data types include geography and upcoming additions, allowing easy access to related information like population and unemployment rate, demonstrated with practical examples.", 'Demonstration of using XLOOKUP function to retrieve specific data, including the store manager based on the country, with step-by-step guidance and practical examples.']}, {'end': 1091.373, 'segs': [{'end': 775.405, 'src': 'heatmap', 'start': 695.653, 'weight': 6, 'content': [{'end': 701.137, 'text': 'if i copy this down now, you see that for china it correctly pulls in oreo baker.', 'start': 695.653, 'duration': 5.484}, {'end': 705.46, 'text': 'as the manager, you can use xlookup for all sorts of neat scenarios.', 'start': 701.137, 'duration': 4.323}, {'end': 707.602, 'text': 'this is just a quick example of how you can use it.', 'start': 705.46, 'duration': 2.142}, {'end': 712.188, 'text': 'Tip number nine, another one of my favorite functions is the sum function.', 'start': 707.783, 'duration': 4.405}, {'end': 720.918, 'text': "You can enter equals and then type in sum and you can very easily sum up all the values right here and that'll tell you the total of the column.", 'start': 712.608, 'duration': 8.31}, {'end': 724.48, 'text': 'There is an easier way and I love this shortcut key.', 'start': 721.318, 'duration': 3.162}, {'end': 733.064, 'text': 'Instead, you can press the Alt key together with the equals key and that will automatically pull in the sum function and sum up all the values above.', 'start': 724.88, 'duration': 8.184}, {'end': 734.605, 'text': 'You can then press enter.', 'start': 733.424, 'duration': 1.181}, {'end': 742.068, 'text': "As an alternative to sum values, you can simply highlight all the values and down in the status bar down below, you'll also see the sum.", 'start': 734.785, 'duration': 7.283}, {'end': 748.712, 'text': 'One additional way that you can sum values, click on insert on the top ribbon and then insert a table and then click on OK.', 'start': 742.369, 'duration': 6.343}, {'end': 753.714, 'text': 'This inserts a table and under table design, I can add a total row.', 'start': 749.392, 'duration': 4.322}, {'end': 759.076, 'text': 'Now, when I click down below here to from a dropdown list, I can select from a number of different functions.', 'start': 754.094, 'duration': 4.982}, {'end': 762.678, 'text': "I'm going to select some and there too, I also have my sum.", 'start': 759.416, 'duration': 3.262}, {'end': 767.94, 'text': 'Tip number 10, I want to show you how you can connect or concatenate different values.', 'start': 763.078, 'duration': 4.862}, {'end': 775.405, 'text': "Here I have cells A and B, Cookie Monster, Kevin Stratford, Excel Tips and Tricks, and I'd like to bring them together in one cell.", 'start': 768.4, 'duration': 7.005}], 'summary': 'Demonstrates using xlookup and sum function for data analysis and shortcuts in excel.', 'duration': 58.061, 'max_score': 695.653, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y695653.jpg'}, {'end': 842.168, 'src': 'heatmap', 'start': 796.636, 'weight': 8, 'content': [{'end': 798.597, 'text': 'This now concatenates those values.', 'start': 796.636, 'duration': 1.961}, {'end': 802.218, 'text': 'But there is an even easier way in Microsoft Excel.', 'start': 798.917, 'duration': 3.301}, {'end': 807.96, 'text': "Instead, I'm going to type in Cookie Monster because this is what I want it to look like in cell C1.", 'start': 802.618, 'duration': 5.342}, {'end': 812.443, 'text': 'Now, instead of entering a formula, I simply drag this down.', 'start': 808.98, 'duration': 3.463}, {'end': 814.064, 'text': 'Now, currently it says Cookie Monster.', 'start': 812.503, 'duration': 1.561}, {'end': 817.667, 'text': 'If I click on this little icon, I can apply a flash fill.', 'start': 814.445, 'duration': 3.222}, {'end': 824.973, 'text': 'And here what Excel does is it detects how I merge these two values and it does the same thing as I flash fill down.', 'start': 817.988, 'duration': 6.985}, {'end': 828.256, 'text': "So I don't even need to use a formula anymore to connect cells.", 'start': 825.233, 'duration': 3.023}, {'end': 835.462, 'text': 'Tip number 11, I want to show you how you can use SUMIF to only sum values if they meet a certain criteria.', 'start': 828.516, 'duration': 6.946}, {'end': 842.168, 'text': 'Here I have profit data by country, so I see all my profit for the United States and I see all of my profit for China.', 'start': 835.882, 'duration': 6.286}], 'summary': 'In excel, flash fill merges values without formulas. sumif sums values meeting criteria.', 'duration': 45.532, 'max_score': 796.636, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y796636.jpg'}, {'end': 864.985, 'src': 'embed', 'start': 835.882, 'weight': 3, 'content': [{'end': 842.168, 'text': 'Here I have profit data by country, so I see all my profit for the United States and I see all of my profit for China.', 'start': 835.882, 'duration': 6.286}, {'end': 847.112, 'text': 'What if I just want to know the profit for the United States? We could use SUMIF to do this.', 'start': 842.548, 'duration': 4.564}, {'end': 851.515, 'text': "Down in this cell, I'm going to enter my formula and we're going to type in SUMIF.", 'start': 847.572, 'duration': 3.943}, {'end': 858.941, 'text': 'Next, I want to open my parentheses and I want to look in column A to see if it matches United States.', 'start': 852.396, 'duration': 6.545}, {'end': 864.985, 'text': "Next, I need to type in my criteria and I'm going to type in United States in quotes.", 'start': 859.481, 'duration': 5.504}], 'summary': 'Using sumif to calculate profit for united states from profit data by country.', 'duration': 29.103, 'max_score': 835.882, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y835882.jpg'}, {'end': 954.368, 'src': 'embed', 'start': 926.144, 'weight': 4, 'content': [{'end': 930.025, 'text': 'Here now I see that we sell fortune cookies in two different countries.', 'start': 926.144, 'duration': 3.881}, {'end': 934.926, 'text': "Here I see in the United States and here I see in China and there's no fortune cookie in Germany.", 'start': 930.405, 'duration': 4.521}, {'end': 942.884, 'text': 'Tip number 13, I wanna show you how you can use the if function to run a logical test on the data in your Excel sheet.', 'start': 935.782, 'duration': 7.102}, {'end': 945.765, 'text': 'You can build some pretty complex if functions.', 'start': 943.224, 'duration': 2.541}, {'end': 948.346, 'text': "I'm gonna start off with just a simple one here.", 'start': 945.945, 'duration': 2.401}, {'end': 954.368, 'text': 'Right here in my data, I have all these different markets and I have the cookie types that we sell in those markets.', 'start': 949.046, 'duration': 5.322}], 'summary': 'Fortune cookies sold in us and china, none in germany. excel if function for logical tests.', 'duration': 28.224, 'max_score': 926.144, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y926144.jpg'}, {'end': 999.245, 'src': 'embed', 'start': 967.954, 'weight': 5, 'content': [{'end': 971.338, 'text': "I'm going to type in if, and then I'm going to open the parentheses.", 'start': 967.954, 'duration': 3.384}, {'end': 974.162, 'text': 'First off, we want to do a logical test.', 'start': 971.759, 'duration': 2.403}, {'end': 978.947, 'text': "So let's say that maybe if the favorability rating is lower than say 25%, we want to investigate.", 'start': 974.502, 'duration': 4.445}, {'end': 989.297, 'text': "So, I'm going to say if this cell is less than 0.25 or 25%, then we are going to investigate.", 'start': 980.649, 'duration': 8.648}, {'end': 993.78, 'text': "Otherwise, if it's greater than that amount, then we'll simply say all good.", 'start': 989.637, 'duration': 4.143}, {'end': 999.245, 'text': "Now that I've typed in my formula, let's press enter and here I see that this is 94%, that's greater than 25%, so all is good.", 'start': 994.021, 'duration': 5.224}], 'summary': 'Using logical test, investigate if favorability rating is less than 25%, else consider it all good.', 'duration': 31.291, 'max_score': 967.954, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y967954.jpg'}, {'end': 1048.324, 'src': 'embed', 'start': 1016.275, 'weight': 1, 'content': [{'end': 1019.077, 'text': 'But you could use a convert function for a lot more than that.', 'start': 1016.275, 'duration': 2.802}, {'end': 1025.438, 'text': "Here we have a store location that opened up on 10-18-2017 and today's date is 10-22-2020.", 'start': 1019.577, 'duration': 5.861}, {'end': 1031.819, 'text': "As an added bonus, you can get today's date by typing in equals today, open and close parentheses.", 'start': 1025.458, 'duration': 6.361}, {'end': 1035.099, 'text': 'To calculate the age in days, this is pretty simple.', 'start': 1032.259, 'duration': 2.84}, {'end': 1041.101, 'text': "We simply insert the equals sign and I take today's date and then I subtract the date that we opened the location.", 'start': 1035.16, 'duration': 5.941}, {'end': 1043.301, 'text': 'Here I could see that the location is 1,100 days old.', 'start': 1041.54, 'duration': 1.761}, {'end': 1048.324, 'text': "Let's say I want to figure out what that is in years.", 'start': 1045.862, 'duration': 2.462}], 'summary': "A convert function can calculate the age of a store location, which opened on 10-18-2017 and today's date is 10-22-2020, making it 1,100 days old.", 'duration': 32.049, 'max_score': 1016.275, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1016275.jpg'}, {'end': 1086.93, 'src': 'embed', 'start': 1055.469, 'weight': 0, 'content': [{'end': 1059.832, 'text': 'With the convert function, you could switch a number from one measurement system to another.', 'start': 1055.469, 'duration': 4.363}, {'end': 1061.313, 'text': "That's exactly what I need.", 'start': 1059.912, 'duration': 1.401}, {'end': 1066.116, 'text': "First, I need to pick the number that I wanna convert, so I'm gonna choose days.", 'start': 1061.833, 'duration': 4.283}, {'end': 1070.899, 'text': "Next, let's insert a comma, and then I need to indicate what unit I'm converting from.", 'start': 1066.616, 'duration': 4.283}, {'end': 1074.141, 'text': 'When I go down this list, I wanna select days.', 'start': 1071.279, 'duration': 2.862}, {'end': 1080.285, 'text': 'Next, I wanna convert it to years, and here, because I selected days, it narrows down my set of options.', 'start': 1074.582, 'duration': 5.703}, {'end': 1086.93, 'text': "I'm going to click on year, and now I can close the parentheses, hit enter, and I see that it's been open for three years.", 'start': 1080.726, 'duration': 6.204}], 'summary': 'Using the convert function, 3 days are switched to 3 years.', 'duration': 31.461, 'max_score': 1055.469, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1055469.jpg'}], 'start': 695.653, 'title': 'Excel tips and functions', 'summary': "Offers excel tips and tricks including xlookup, sum function, and concatenation, with a demo of shortcuts. it also demonstrates the usage of sumif, countif, and if functions with examples. additionally, it showcases calculating date differences and converting units using the 'convert' function, resulting in an age of 3 years and 100 days.", 'chapters': [{'end': 817.667, 'start': 695.653, 'title': 'Excel tips and tricks', 'summary': 'Presents excel tips including the usage of xlookup, sum function, and concatenation, with a demonstration of shortcuts and alternative methods, providing a comprehensive understanding of the features.', 'duration': 122.014, 'highlights': ['The manager can use xlookup for various scenarios, such as pulling in specific data for China, exemplifying its versatility and practical application.', 'The shortcut Alt key together with the equals key automatically pulls in the sum function, simplifying the process of summing up values, contributing to increased efficiency in Excel usage.', 'The demonstration of using flash fill for concatenating different values in Microsoft Excel, providing an easier and quicker alternative to using formulas and functions.', 'Inserting a table and utilizing the total row feature in Excel to sum up values, demonstrating an alternative method to the sum function, showcasing the versatility of Excel for data manipulation and analysis.']}, {'end': 993.78, 'start': 817.988, 'title': 'Excel functions tips and examples', 'summary': 'Demonstrates how to use sumif to sum values meeting a certain criteria, countif to count values meeting a certain criteria, and if function to run a logical test on the data in excel, showcasing examples and results for each function.', 'duration': 175.792, 'highlights': ['The chapter demonstrates how to use SUMIF to sum values meeting a certain criteria The speaker shows how to use the SUMIF function to sum profit data for the United States, resulting in a profit just shy of 3 million.', 'The chapter showcases examples and results for each function Various examples and results are showcased, including using COUNTIF to count countries selling fortune cookies, resulting in two countries selling fortune cookies.', 'The chapter demonstrates how to use IF function to run a logical test on the data in Excel The speaker demonstrates how to use the IF function to run a logical test based on favorability ratings, indicating when to investigate based on the ratings.']}, {'end': 1091.373, 'start': 994.021, 'title': 'Calculating date differences and converting units', 'summary': "Demonstrates calculating the age of a store location in days and converting it to years using the 'convert' function, resulting in an age of 3 years and 100 days, showcasing the versatility of the function for various measurement system conversions.", 'duration': 97.352, 'highlights': ['The convert function is used to switch a number from one measurement system to another, as demonstrated by converting the age of the store location from days to years, resulting in a precise age of 3 years and 100 days.', 'The chapter showcases the calculation of the age of a store location, resulting in a clear understanding that the location is 1,100 days old and can be converted to 3 years using the convert function, highlighting the practical application of the function.', 'The tutorial provides insights into calculating the difference between dates, showcasing the process of obtaining the age of a store location in days and then converting it to years using the convert function, revealing the simplicity and effectiveness of the process.', "The demonstration emphasizes the versatility of the convert function for multiple measurement system conversions, illustrating the ease of switching from days to years with precision, enhancing the understanding of the function's capabilities."]}], 'duration': 395.72, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y695653.jpg', 'highlights': ['The convert function is used to switch a number from one measurement system to another, as demonstrated by converting the age of the store location from days to years, resulting in a precise age of 3 years and 100 days.', 'The tutorial provides insights into calculating the difference between dates, showcasing the process of obtaining the age of a store location in days and then converting it to years using the convert function, revealing the simplicity and effectiveness of the process.', "The demonstration emphasizes the versatility of the convert function for multiple measurement system conversions, illustrating the ease of switching from days to years with precision, enhancing the understanding of the function's capabilities.", 'The chapter demonstrates how to use SUMIF to sum values meeting a certain criteria The speaker shows how to use the SUMIF function to sum profit data for the United States, resulting in a profit just shy of 3 million.', 'The chapter showcases examples and results for each function Various examples and results are showcased, including using COUNTIF to count countries selling fortune cookies, resulting in two countries selling fortune cookies.', 'The chapter demonstrates how to use IF function to run a logical test on the data in Excel The speaker demonstrates how to use the IF function to run a logical test based on favorability ratings, indicating when to investigate based on the ratings.', 'The manager can use xlookup for various scenarios, such as pulling in specific data for China, exemplifying its versatility and practical application.', 'The shortcut Alt key together with the equals key automatically pulls in the sum function, simplifying the process of summing up values, contributing to increased efficiency in Excel usage.', 'The demonstration of using flash fill for concatenating different values in Microsoft Excel, providing an easier and quicker alternative to using formulas and functions.', 'Inserting a table and utilizing the total row feature in Excel to sum up values, demonstrating an alternative method to the sum function, showcasing the versatility of Excel for data manipulation and analysis.']}, {'end': 1211.675, 'segs': [{'end': 1116.206, 'src': 'embed', 'start': 1091.893, 'weight': 2, 'content': [{'end': 1097.976, 'text': 'Tip number 15, I want to show you how you could very easily adjust column widths and row heights automatically.', 'start': 1091.893, 'duration': 6.083}, {'end': 1102.899, 'text': 'Now here in my data, you see a few of the rows are a little off and a few of the columns are a little off.', 'start': 1098.337, 'duration': 4.562}, {'end': 1108.782, 'text': 'Now I can click between columns and I can drag it larger to make sure that the data shows up.', 'start': 1103.259, 'duration': 5.523}, {'end': 1110.403, 'text': 'That takes a lot of manual effort.', 'start': 1108.802, 'duration': 1.601}, {'end': 1116.206, 'text': "Now I can also click in between and that'll automatically adjust it, but I have a lot of columns to go through.", 'start': 1110.423, 'duration': 5.783}], 'summary': 'Learn to easily adjust column widths and row heights in data presentation.', 'duration': 24.313, 'max_score': 1091.893, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1091893.jpg'}, {'end': 1154.675, 'src': 'embed', 'start': 1129.917, 'weight': 1, 'content': [{'end': 1136.843, 'text': 'Similarly, I could click on the space in between the two rows and then this also automatically adjusts all of the rows.', 'start': 1129.917, 'duration': 6.926}, {'end': 1140.385, 'text': 'Tip number 16, you can freeze panes in your Excel sheet.', 'start': 1137.083, 'duration': 3.302}, {'end': 1142.547, 'text': 'Here when I scroll down, I lose the headers.', 'start': 1140.445, 'duration': 2.102}, {'end': 1149.612, 'text': 'If I want to keep them up here, I go up to view on the top toolbar and I can go over to this option that says freeze panes.', 'start': 1142.747, 'duration': 6.865}, {'end': 1151.713, 'text': 'I can then freeze the top row.', 'start': 1150.072, 'duration': 1.641}, {'end': 1154.675, 'text': 'So when I freeze the top row, now my column headers stay.', 'start': 1151.994, 'duration': 2.681}], 'summary': 'Freezing the top row in excel can keep column headers visible while scrolling.', 'duration': 24.758, 'max_score': 1129.917, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1129917.jpg'}, {'end': 1195.149, 'src': 'embed', 'start': 1168.888, 'weight': 0, 'content': [{'end': 1173.154, 'text': 'so it freezes it in that specific location and you could do the same with columns as well.', 'start': 1168.888, 'duration': 4.266}, {'end': 1178.18, 'text': 'Tip number 17 is pivot tables and pivot tables are extremely valuable.', 'start': 1173.354, 'duration': 4.826}, {'end': 1184.606, 'text': "In fact, using pivot tables, you don't have to enter as many formulas anymore in Microsoft Excel.", 'start': 1178.581, 'duration': 6.025}, {'end': 1188.587, 'text': 'To insert a pivot table, simply select anywhere within your data set.', 'start': 1184.906, 'duration': 3.681}, {'end': 1191.748, 'text': 'And first off, you could insert recommended pivot tables.', 'start': 1188.967, 'duration': 2.781}, {'end': 1195.149, 'text': "So here it'll pre-populate the pivot table for you.", 'start': 1192.028, 'duration': 3.121}], 'summary': 'Using pivot tables in microsoft excel reduces the need for many formulas, making data analysis more efficient.', 'duration': 26.261, 'max_score': 1168.888, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1168888.jpg'}], 'start': 1091.893, 'title': 'Excel tips and tricks', 'summary': 'Demonstrates adjusting column widths and row heights, freezing panes, and utilizing pivot tables to reduce formula entry in microsoft excel.', 'chapters': [{'end': 1211.675, 'start': 1091.893, 'title': 'Excel tips and tricks', 'summary': 'Demonstrates how to adjust column widths and row heights automatically, freeze panes to keep headers visible, and utilize pivot tables to reduce the need for entering formulas in microsoft excel.', 'duration': 119.782, 'highlights': ["You can freeze panes in your Excel sheet to keep headers visible. Freezing panes can be done by going to the 'View' tab, selecting 'Freeze Panes', and choosing to freeze the top row or first column to keep headers visible.", 'Pivot tables are extremely valuable as they reduce the need to enter formulas in Microsoft Excel. By inserting pivot tables, users can easily analyze and summarize data without having to manually enter complex formulas, thus improving efficiency and accuracy in data analysis.', 'You can adjust column widths and row heights automatically in Excel. By selecting the entire sheet and adjusting the columns and rows, users can ensure that the data fits appropriately without the need for manual adjustments, saving time and effort.']}], 'duration': 119.782, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1091893.jpg', 'highlights': ['Pivot tables reduce the need for entering formulas in Excel, improving efficiency and accuracy in data analysis.', 'Freezing panes in Excel keeps headers visible, enhancing user experience.', 'Automatic adjustment of column widths and row heights in Excel saves time and effort.']}, {'end': 1523.918, 'segs': [{'end': 1247.279, 'src': 'embed', 'start': 1211.815, 'weight': 0, 'content': [{'end': 1215.357, 'text': "I also have a more in-depth video in the description if you're interested.", 'start': 1211.815, 'duration': 3.542}, {'end': 1220.06, 'text': 'Here, I could pull different fields down into these rectangles down below.', 'start': 1215.817, 'duration': 4.243}, {'end': 1225.843, 'text': "For instance, I'm going to pull the country down into rows and here I can see how many countries that we're currently operating in.", 'start': 1220.16, 'duration': 5.683}, {'end': 1230.206, 'text': 'If I want to see profit by country, I click on profit and I can drag it into values.', 'start': 1226.363, 'duration': 3.843}, {'end': 1236.651, 'text': "So here I see in the United States, we have just shy of 3 million and in China, we're just short of 1 million.", 'start': 1230.526, 'duration': 6.125}, {'end': 1241.875, 'text': 'I could also pull cookie in and then here I could see the country with the cookies and what the profit is.', 'start': 1236.971, 'duration': 4.904}, {'end': 1247.279, 'text': 'I can right click on this and I can even sort from largest to smallest to see what the sales look like.', 'start': 1242.235, 'duration': 5.044}], 'summary': 'Demonstrates data visualization and analysis using profit figures of 3 million in the us and nearly 1 million in china, along with sorting capabilities.', 'duration': 35.464, 'max_score': 1211.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1211815.jpg'}, {'end': 1307.44, 'src': 'embed', 'start': 1266.713, 'weight': 3, 'content': [{'end': 1269.835, 'text': "this chart will update based on what i'm doing in the table.", 'start': 1266.713, 'duration': 3.122}, {'end': 1274.118, 'text': "pivot tables and pivot charts are extremely powerful and it's definitely worth exploring.", 'start': 1269.835, 'duration': 4.283}, {'end': 1279.863, 'text': 'Tip number 18, you can use drop-down lists to reduce errors with entering data.', 'start': 1274.338, 'duration': 5.525}, {'end': 1284.326, 'text': 'Here, I want people to insert a store manager for these different locations.', 'start': 1280.263, 'duration': 4.063}, {'end': 1291.132, 'text': 'I have a table down below and someone could come in and they could write the name out or they could simply select it from a drop-down list.', 'start': 1284.706, 'duration': 6.426}, {'end': 1296.256, 'text': "How do we insert a drop-down list? Well, first off, let's go up to the top and click on data.", 'start': 1291.532, 'duration': 4.724}, {'end': 1301.638, 'text': "Within data, let's go over to the data tools and click on the data validation icon.", 'start': 1296.556, 'duration': 5.082}, {'end': 1307.44, 'text': 'This opens up a prompt and we can set it so we allow values from a list.', 'start': 1302.238, 'duration': 5.202}], 'summary': 'Pivot tables and charts are powerful; use drop-down lists to reduce data entry errors.', 'duration': 40.727, 'max_score': 1266.713, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1266713.jpg'}, {'end': 1453.674, 'src': 'embed', 'start': 1422.999, 'weight': 5, 'content': [{'end': 1427.801, 'text': 'and here I could indicate how often I want Microsoft Excel to update this data.', 'start': 1422.999, 'duration': 4.802}, {'end': 1433.964, 'text': 'For instance I could have it refresh every 60 minutes or I could simply set it to refresh the data whenever I open this file.', 'start': 1427.881, 'duration': 6.083}, {'end': 1437.566, 'text': 'tip number 20, and this is the last tip of today.', 'start': 1434.244, 'duration': 3.322}, {'end': 1442.889, 'text': "i know sad i'm shedding tears over here on my end to collaborate with others.", 'start': 1437.566, 'duration': 5.323}, {'end': 1446.951, 'text': "let's go up to the top right hand corner and there's a button that says share.", 'start': 1442.889, 'duration': 4.062}, {'end': 1453.674, 'text': 'if you save your excel file in one Drive or in SharePoint, you can very easily share your file with others.', 'start': 1446.951, 'duration': 6.723}], 'summary': 'Excel can be set to refresh data every 60 minutes or upon opening. files can be easily shared in onedrive or sharepoint.', 'duration': 30.675, 'max_score': 1422.999, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1422999.jpg'}, {'end': 1491.748, 'src': 'embed', 'start': 1464.898, 'weight': 6, 'content': [{'end': 1471.7, 'text': 'Here too, I could specify specific email addresses to share with or I could simply copy a link and then share it elsewhere.', 'start': 1464.898, 'duration': 6.802}, {'end': 1474.901, 'text': 'Once I share the file with others up in the top right hand corner,', 'start': 1472.1, 'duration': 2.801}, {'end': 1479.603, 'text': "I could click on comments and I could insert comments for the other people who I'm working with.", 'start': 1474.901, 'duration': 4.702}, {'end': 1485.346, 'text': 'I could work with any number of people on this Excel spreadsheet and we could all work together in real time.', 'start': 1480.003, 'duration': 5.343}, {'end': 1486.206, 'text': 'All right.', 'start': 1485.646, 'duration': 0.56}, {'end': 1491.748, 'text': 'That was a quick look at my top favorite 20 tips and tricks in Microsoft Excel.', 'start': 1486.246, 'duration': 5.502}], 'summary': 'In microsoft excel, multiple people can collaborate in real time, and specific email addresses or links can be used to share files. additionally, comments can be inserted for collaboration.', 'duration': 26.85, 'max_score': 1464.898, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1464898.jpg'}], 'start': 1211.815, 'title': 'Data visualization and excel tips & tricks', 'summary': 'Demonstrates a data visualization demo with profit data for the united states and china, and provides tips on using pivot tables, pivot charts, data validation, web data import, and collaboration in real-time with data refresh intervals.', 'chapters': [{'end': 1247.279, 'start': 1211.815, 'title': 'Data visualization demo', 'summary': 'Demonstrates a data visualization demo using rectangles and fields to display profit by country, showing just shy of 3 million in the united states and just short of 1 million in china, and also allowing sorting of sales from largest to smallest.', 'duration': 35.464, 'highlights': ['The demonstration involves pulling different fields into rectangles to visualize data, such as pulling the country down into rows to see the number of countries currently operating in.', 'Profit by country is shown, with just shy of 3 million in the United States and just short of 1 million in China, and the ability to view sales by sorting from largest to smallest.', 'The visualization also includes the ability to see the country with the cookies and their corresponding profit, enhancing the understanding of sales data.']}, {'end': 1523.918, 'start': 1247.599, 'title': 'Excel tips & tricks: data visualization, data validation, web data import, and collaboration', 'summary': 'Provides tips on data visualization using pivot tables and pivot charts, data validation to reduce errors, importing data from the web into excel, and collaborating with others in real-time, including setting data refresh intervals.', 'duration': 276.319, 'highlights': ['Pivot tables and pivot charts are extremely powerful for data visualization and are worth exploring. The chapter highlights the power of pivot tables and pivot charts for data visualization, emphasizing their worth for exploration.', 'Using drop-down lists for data validation can reduce errors in entering data. The chapter emphasizes the use of drop-down lists for data validation to minimize errors in entering data.', 'Importing data from the web into Excel allows for automatic updates, with the ability to set data refresh intervals. The chapter explains the process of importing data from the web into Excel, highlighting the automatic updates and the option to set data refresh intervals.', 'Collaboration in Excel is facilitated through sharing options, real-time editing, and commenting features. The chapter details the collaboration features in Excel, including sharing options, real-time editing, and commenting functionalities.']}], 'duration': 312.103, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AOPa7ORJj4Y/pics/AOPa7ORJj4Y1211815.jpg', 'highlights': ['The demonstration involves pulling different fields into rectangles to visualize data, such as pulling the country down into rows to see the number of countries currently operating in.', 'Profit by country is shown, with just shy of 3 million in the United States and just short of 1 million in China, and the ability to view sales by sorting from largest to smallest.', 'The visualization also includes the ability to see the country with the cookies and their corresponding profit, enhancing the understanding of sales data.', 'Pivot tables and pivot charts are extremely powerful for data visualization and are worth exploring.', 'Using drop-down lists for data validation can reduce errors in entering data.', 'Importing data from the web into Excel allows for automatic updates, with the ability to set data refresh intervals.', 'Collaboration in Excel is facilitated through sharing options, real-time editing, and commenting features.']}], 'highlights': ['You can convert a photo into Microsoft Excel using the Microsoft Office app, allowing import of specific data like dates, impressions, and clicks.', 'Excel can be accessed for free on the web via office.com, offering commonly used controls directly in Excel on the web.', 'Using ideas in Microsoft Excel allows for automatic analysis of data and generation of insightful views, such as identifying the majority of sales coming from the United States.', 'The chapter emphasizes the importance of defining cell names for friendlier and more understandable formulas.', 'The chapter introduces the concept of recording and using macros, setting the stage for further exploration of its potential applications.', 'The chapter demonstrates how to use macros in Excel to automate repetitive tasks, saving time and reducing manual steps.', 'An example is provided where a macro is created to add additional countries and automate the process of copying and pasting cookie types, showcasing the efficiency and time-saving benefits of using macros in Excel.', 'The use of macros in the example results in the reduction of manual steps and an increase in efficiency, demonstrating the practical benefits of automating tasks in Excel.', "Excel's new data types include geography and upcoming additions, allowing easy access to related information like population and unemployment rate, demonstrated with practical examples.", 'Demonstration of using XLOOKUP function to retrieve specific data, including the store manager based on the country, with step-by-step guidance and practical examples.', 'The convert function is used to switch a number from one measurement system to another, as demonstrated by converting the age of the store location from days to years, resulting in a precise age of 3 years and 100 days.', 'The tutorial provides insights into calculating the difference between dates, showcasing the process of obtaining the age of a store location in days and then converting it to years using the convert function, revealing the simplicity and effectiveness of the process.', "The demonstration emphasizes the versatility of the convert function for multiple measurement system conversions, illustrating the ease of switching from days to years with precision, enhancing the understanding of the function's capabilities.", 'The chapter demonstrates how to use SUMIF to sum values meeting a certain criteria The speaker shows how to use the SUMIF function to sum profit data for the United States, resulting in a profit just shy of 3 million.', 'The chapter showcases examples and results for each function Various examples and results are showcased, including using COUNTIF to count countries selling fortune cookies, resulting in two countries selling fortune cookies.', 'The chapter demonstrates how to use IF function to run a logical test on the data in Excel The speaker demonstrates how to use the IF function to run a logical test based on favorability ratings, indicating when to investigate based on the ratings.', 'The manager can use xlookup for various scenarios, such as pulling in specific data for China, exemplifying its versatility and practical application.', 'The shortcut Alt key together with the equals key automatically pulls in the sum function, simplifying the process of summing up values, contributing to increased efficiency in Excel usage.', 'The demonstration of using flash fill for concatenating different values in Microsoft Excel, providing an easier and quicker alternative to using formulas and functions.', 'Inserting a table and utilizing the total row feature in Excel to sum up values, demonstrating an alternative method to the sum function, showcasing the versatility of Excel for data manipulation and analysis.', 'Pivot tables reduce the need for entering formulas in Excel, improving efficiency and accuracy in data analysis.', 'Freezing panes in Excel keeps headers visible, enhancing user experience.', 'Automatic adjustment of column widths and row heights in Excel saves time and effort.', 'The demonstration involves pulling different fields into rectangles to visualize data, such as pulling the country down into rows to see the number of countries currently operating in.', 'Profit by country is shown, with just shy of 3 million in the United States and just short of 1 million in China, and the ability to view sales by sorting from largest to smallest.', 'The visualization also includes the ability to see the country with the cookies and their corresponding profit, enhancing the understanding of sales data.', 'Pivot tables and pivot charts are extremely powerful for data visualization and are worth exploring.', 'Using drop-down lists for data validation can reduce errors in entering data.', 'Importing data from the web into Excel allows for automatic updates, with the ability to set data refresh intervals.', 'Collaboration in Excel is facilitated through sharing options, real-time editing, and commenting features.']}