Analyzing and forecasting data in Excel 365 (2024)
Excel 365 (2024)
| Intermediate
- 13 videos | 59m 2s
- Includes Assessment
- Earns a Badge
- CPE
Excel 365 is a powerful tool for data management tasks, such as consolidating, analyzing, and forecasting. This course offers a deep dive into these advanced features of Excel 365 to optimize how to handle data from different sources and get useful insights. First, discover the different What-If Analysis tools to evaluate different scenarios. Learn how to use the Scenario Manager tool to compare different data outcomes and delve into What-If Analysis with data tables to perform sensitivity analysis. The course further explores Goal Seek for achieving specific data outcomes and employs the Solver add-in for solving complex optimization problems. Next, discover different tools to forecast data in Excel. For example, use the Forecast Sheet tool for visual predictions. Financial analysis is not overlooked, as the course covers estimating loan-related costs using Excel functions such as PMT, IPMT, and PPMT. Excel also provides two intelligent tools to get insights on how to analyze your data. Discover the Analyze Data feature for AI-powered analysis, and get to grips with the Copilot tool for advanced data insights through natural language processing. Lastly, discover how to consolidate data from various sources for streamlining the process of analysis and reporting. Explore Power Query, an essential part of Excel for data importation, transformation, and efficient data source management, including maintaining connections and automating data refreshes. This course aligns with the objectives of Exam MO-211: Microsoft Excel Expert (Microsoft 365 Apps). Would you like hands-on practice? Go to the Course Contents pane and download the sample files to follow along with select videos.
WHAT YOU WILL LEARN
-
Discover the key concepts covered in this analyzing and forecasting data in excel 365 (2024) course
Use the scenario manager tool to observe different outcomes
Use data tables for what-if analysis in excel 365
Use goal seek to modify a data variable and reach a target value
Use the solver add-in to analyze constraints and variables and optimize data values
Use the forecast sheet tool to display trends and predictions based on available historic data
Forecast data with formulas in excel 365 -
Use the pmt, ipmt, and ppmt functions to estimate loan-related costs in excel 365
Use the analyze data tool in excel 365
Get data insights and add calculated columns with copilot for excel
Use the consolidate feature in excel 365
Use the power query tool in excel 365
Manage data sources using the power query tool in excel 365
IN THIS COURSE
-
1mThis video outlines the key content covered in this Analyzing and forecasting data in Excel 365 (2024) course, including using the Scenario Manager and Data tables to evaluate different scenarios, forecasting data with formulas and the Forecast Sheet tool, and importing data with Power Query. FREE ACCESS
-
3m 50sThe Scenario Manager in Excel is a powerful tool for running what-if analyses. It enables users to define and store various sets of input values, called scenarios, that can be easily switched to observe different outcomes. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform what-if analysis by using Goal Seek and Scenario Manager. FREE ACCESS
-
3. Using data tables for What-if Analysis in Excel 3655m 3sData tables are a great way of testing different what-if scenarios when analyzing data. Discover how to use data tables to create calculations using one or two variables and once the data table has been created, adjust the values and change the calculation options. FREE ACCESS
-
4. Finding a result with Goal Seek in Excel 3654m 7sThe Goal Seek tool can be used to determine which variables need to change in order to reach a target value in a table obtained using formulas, without the need for manual adjustments to a formula. In this video, explore how to use the Goal Seek tool to modify data variables to reach a target value in Excel 365. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform what-if analysis by using Goal Seek and Scenario Manager. FREE ACCESS
-
5. Solving a problem in Excel 3655m 13sIf a mathematical problem exists within your table and is subject to a range of constraints, the Solver add-in in Excel can be used to attempt an automatic solution. The desired value to be optimized, minimized, or adjusted in another manner should be selected, and the constraints influencing the outcome must be entered. FREE ACCESS
-
6. Using the Forecast Sheet tool in Excel 3654m 32sIn Excel 365, use one-click forecasting to make predictions based on historical data. Learn how to adjust the timeline and seasonality settings, and make changes to the forecast data once the sheet has been inserted. In this video, explore how to use the Forecast Sheet tool to predict trends and generate a forecast based on time-based data already in a table or range. FREE ACCESS
-
7. Forecasting data with formulas in Excel 3655m 36sTo calculate how many months it would take to reimburse a loan according to a fixed monthly payment and an interest rate, use the NPER function in Excel 365. In this video, learn how to combine the NPER function with the IF and AND functions and forecast data that meets one or more criteria. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Forecast data by using the AND(), IF(), and NPER() functions. FREE ACCESS
-
8. Estimating loan-related costs in Excel 3655m 34sIf a loan has been taken out, Excel 365 can provide assistance in estimating and managing repayments. It is possible to perform calculations for total repayments per period, as well as interest and principal repayments. In this video, learn how to use the PMT, IPMT, and PPMT functions to estimate loan-related costs. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Calculate financial data by using the PMT() function. FREE ACCESS
-
9. Performing analysis with Analyze Data in Excel 3653m 14sThe Analyze Data tool in Excel 365 can perform an analysis of a dataset to look for patterns in the data to make clever, personalized recommendations for you. In this video, you will explore how to use the Analyze Data tool in Excel 365. FREE ACCESS
-
10. Analyzing data with Copilot in Excel 3655m 21sCopilot for Microsoft Excel 365 acts as your AI-powered data analyst. In this video, you will learn how to use Copilot to get insights about your data in the form of charts and other objects. See how to highlight significant values and sort your data. Finally, discover how to add calculated columns to your table using Copilot. FREE ACCESS
-
11. Consolidating data in Excel 3655m 12sData from multiple worksheets or workbooks can be extracted and combined into a single table using Excel 365's powerful Consolidate function. In this video, learn how to configure data consolidation options and how to add ranges from different worksheets. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Summarize data from multiple ranges by using the Consolidate feature. FREE ACCESS
-
12. Querying data sources with Power Query in Excel 3655m 3sWith Power Query in Excel 365, import or connect to an external data source, such as a database, without affecting the original dataset. Next, shape the data in ways that meet your needs to load it into a query in Excel to create charts and reports. In this video, explore how to use Power Query in Excel 365. FREE ACCESS
-
13. Managing data sources with Power Query in Excel 3655m 16sIn Excel 365, Power Query is a powerful tool to import data and then transform and reshape it as needed. Learn how to change a column’s data type, delete and add columns; and even combine data from two different sources, in order to get a fresh analytical view. In this video, explore how to manage data sources with Power Query in Excel. FREE ACCESS
EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE
Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.
Digital badges are yours to keep, forever.
