Referencing Data in Excel 2019 for Windows
Excel 2019 (Windows)
| Intermediate
- 10 videos | 43m 35s
- Includes Assessment
- Earns a Badge
- CPE
Manipulating data in Excel begins with knowing how to reference cells. This involves using cell coordinates to extract the specified cells value. You can reference individual cells, a range of cells and entire columns and rows. In this 10-video course, you will discover absolute and relative cell references, by using data across multiple worksheets, and formula auditing in Excel 2019. Key concepts covered in this course include how to reference cells in your Excel worksheet; how to use data across multiple worksheets to reference data for formulas; and how to define absolute and relative cell references. Next, you will learn how to create named ranges to refer to data and facilitate calculations; how to use the Name Manager to visualize, edit, and delete existing named ranges; and how to perform calculations using constant values. Finally, learn how to use cell references indirectly; how to monitor data entries for changes and errors; how to activate and configure automatic updates; and how to use formula auditing tools to track data. In order to practice what you have learned, you will find the Word document named Excel 2019 for Windows: Referencing Data Exercise as well as the associated materials in the Resources section.
WHAT YOU WILL LEARN
-
Reference cells in a worksheet
Use data across multiple worksheets
Use absolute and relative cell references
Name a data range
Manage named ranges -
Perform calculations using constant values
Use cell references indirectly
Keep an eye on data
Deactivate automatic updates
Use formula auditing tools
IN THIS COURSE
-
5m 32sIf you want to use the contents of a particular cell or cell range, then you must know how to reference them. This involves inserting the coordinates of your cell in order to extract the value it contains. If you want to begin manipulating your data in Excel, you are going to need to know how to reference your cells correctly. FREE ACCESS
-
5m 9sWhen storing and using data on several different worksheets in your document, it is important to know how to reference them correctly in your formulas. In Excel, you can even refer to data contained in completely different workbooks. FREE ACCESS
-
3. Choosing between absolute & relative cell references in Excel 2019 for Windows5m 17sMoving or automating a formula in Excel will automatically adjust the cell references that it contains. Any references to cells, columns and rows contained in these formulas are known as relative cell references. If you want to avoid any adjustments being made to your formula's cell references, you will need to learn how to use the dollar sign ($). FREE ACCESS
-
4. Naming your data ranges in Excel 2019 for Windows4m 43sNaming your Excel data ranges can allow you to refer to and use them later on. Once you have named your data range, you can easily incorporate it into your calculations, drop-down menus, and general navigation. FREE ACCESS
-
5. Managing named ranges in Excel 2019 for Windows2m 34sOnce you have created named ranges in Excel, it's important to understand how to edit, adjust and manage them, as your changes will have an impact wherever the named range is in use. See how to visualize named ranges, edit them, and remove them if they are no longer of use. FREE ACCESS
-
6. Performing calculations using constant values in Excel 2019 for Windows4m 50sYou will often use constant data values when working on your Excel table. If one of your formulas is using a fixed value, then you may find it useful to use the value's cell reference, rather than the value itself. Using the data value in this way can ensure that if you have to make any changes to your fixed value, you only need to change the reference cell, and not every formulas that uses that value. This can save you a lot of time in the long run. FREE ACCESS
-
7. Using cell references indirectly in Excel 2019 for Windows4m 28sThe INDIRECT formula can be used in Excel to refer to a cell indirectly. This can be very useful if you want to be able to manipulate the data referenced in certain calculations in your spreadsheet without having to adjust the formulas manually. FREE ACCESS
-
8. Keeping an eye on your data in Excel 2019 for Windows4m 19sThere are two very useful tools available in Excel which can be used to keep an eye on your data entries. These tools can be used to help you insert your formula arguments correctly and even make corrections to data entries stored in different locations in your workbook. FREE ACCESS
-
9. Activating automatic updates in Excel 2019 for Windows2m 48sValues produced by formulas that you have inserted into your Excel worksheet are by default automatically kept up to date. This means that if data that your formula is using is modified at some point, your calculation will be adjusted to take into account the change in value. You can, however, deactivate this automatic update, and choose when your values are updated yourself. FREE ACCESS
-
10. Using formula auditing in Excel 2019 for Windows3m 56sIf you are unable to understand how your formula has been constructed, you can use the Excel formula auditing tools. These can show you which cells are used in a formula, and how formulas are dependent on certain data ranges. You can even see if your formula uses data taken from a completely different worksheet. 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.
