OmniDocExcel function self-study route: How to choose free video tutorials
For self-learners, this video tutorial explains how to select Excel functions, and gives the practice sequence from single functions to nested combinations, with checking methods. It can be used in any spreadsheet.
Key points of this article
- When choosing a video, first check whether you can follow the operation and whether there are supporting exercise files. The number of episodes is not critical.
- Learn in the order of "single function, conditional combination, search and match, and nesting", which is more stable than jumping to see advanced functions.
- Use a sales breakdown sheet you prepared to practice, and write a formula for each function you learn.
- Use another method to check the formula after calculating it. There are fixed troubleshooting ideas for common error values.
If you want to learn Excel functions by yourself, most of the things you search for are playlists such as "Complete Self-Study Videos of Excel Function Tutorials". There are often dozens of episodes. When you click on them, you don't know which episode to start. Many people have watched it for several hours and only know SUM and AVERAGE. They get stuck when they encounter VLOOKUP or IF nesting. The problem is often not the video itself, but the learning sequence and practice methods.
This article is written for people who have basic computer skills and want to learn Excel functions by themselves through free videos. After reading, you will get a route that can be directly applied: first determine what type of videos you should watch, then learn in a fixed order, practice with your own data, and learn to check the results. The method here does not rely on a specific video and can be used with any spreadsheet software, including the UniCell spreadsheet in the OmniDoc desktop client. OmniDoc does not provide these teaching videos. The following only explains how to choose and how to learn.
How to choose a video to teach yourself Excel functions? One sentence conclusion: first determine the scope according to the work needs, and then choose a series that can be followed by practice
When choosing videos, give priority to series that"can follow the operation, have supporting exercise files, and are divided into chapters according to functions."Don’t be intimidated or attracted by the number of episodes. The table below can help you find your seat.
| your situation | Recommended practices | reason |
|---|---|---|
| Mainly handles daily reports, including summing, filtering, and statistics based on conditions | A short series of lectures on SUM, SUMIFS, and COUNTIFS, plus data perspective | These functions are used a lot in work, and you can use them in your own forms after learning them. |
| It is often necessary to compare data between two tables | Select the chapter dedicated to finding matches (VLOOKUP, XLOOKUP, INDEX and MATCH) to focus on | The concepts of these functions are similar, and it is easier to distinguish the differences through centralized comparison. |
| The foundation is very weak and I am not familiar with cell references. | Let’s first look at the introductory lessons on relative references, fixed references and formula input. | The reference is not clear, and the subsequent function reports an error and I can’t understand the reason. |
| Prepare for exams or interviews | Choose a complete course organized by knowledge points and with exercises | System coverage is required, and the questions can expose your weak links. |
| I’ve watched a lot of videos, but I can’t do it independently. | Pause the video and switch the demonstrated operations to your own form and redo them. | If you just don’t practice, you can’t remember the grammar, it will become ineffective if you switch to new data. |
Before you start: Confirm these points first
- Whether the software version supports the functions in the video. New functions such as XLOOKUP and FILTER are not available in the old version. If you copy the formula, you will get the #NAME? error. Before starting, you can enter the function name in the cell and see if the software can automatically prompt.
- Whether the source of the video is reliable. Priority is given to content that has a clear author or course institution and provides practice files. Compressed packages from unknown sources may contain malicious programs. Please confirm the source before downloading.
- Is there any accompanying exercise file? There are no videos of practice files. You have to create the data yourself. The cost of practice will be much higher and the learning effect will be reduced.
- Whether the exercise data is publicly available. Do not upload original forms containing ID numbers, mobile phone numbers, and customer lists to unknown websites. It is safer to use desensitized or self-compiled data during practice.
- Learning rhythm. Set it a few times a week and only learn one or two functions each time. Don’t read the entire playlist in one go. Functions are remembered by repeated use, not by reading them.
Steps to practice functions using a sales detail table
The following steps take "a sales detailed list" as the practice object and are suitable for following any set of videos simultaneously.
- Prepare practice data. Create a new sales detail table with about 200 rows. The columns include date, region, salesperson, product, unit price, quantity, and amount. You can compile the data yourself, as long as the structure is close to the real table.
- Practice a single function first. After each video chapter, write a corresponding formula next to the table. For example, use SUM to calculate the total amount, and use COUNTIF to count the number of orders in a certain region.
- Practice condition combinations. Use IF and SUMIFS together, for example, to count the sales amount of a certain salesperson in a certain region. First explain the conditions clearly verbally, and then write the formula manually.
- Practice finding matches. Create another product price list and use the search function to automatically fill in the unit price back to the sales table. Deliberately delete a product from the price list and observe where the incorrect values appear.
- Practice nesting. Write two levels of functions into the same cell, for example, judge first and then sum. Before writing, use auxiliary columns to split the blank columns, gradually confirm the results of each layer, and then merge them into one formula after confirming that they are correct.
- Cross-check with a pivot table. Calculate the same statistic once with a formula and then again with a pivot table. If the two do not match, go back and check the scope and conditions of the formula.
- Save as template. Save the exercise sheet as a template and reuse the structure directly when encountering similar problems in the future without having to build it from scratch.
If you are doing the exercises with the UniCell table in the OmniDoc desktop client, the steps are the same as above. However, whether the functions demonstrated in the video can run completely consistently in UniCell requires you to try it yourself in the table before deciding whether to follow it. The download address is download.omnidoc.top.
Check each item after completion
After each batch of functions is learned, or before the formulas are used in formal reports, check them one by one according to the table below.
| Check items | How to see | What if there's a problem? |
|---|---|---|
| Is the result correct? | Use another method to calculate the same number, such as screening first and then summing, and comparing it with the result of the formula | In case of inconsistency, divide the formula into several segments and view the intermediate results one by one. |
| Is the citation correct? | Copy the formula down or to the right to see if the quoted rows change as expected | A fixed address is required, and the $symbol is locked before the column label and line number |
| Error value | Check to see if # N VALUE A, # VALUE!, # NAME?, # DIV/0! | # NAME? Mostly because the function name is misspelled or the version does not support it; # Nash A usually means that the lookup value does not exist in the source data |
| Digital format | See if the numbers are aligned to the left, or whether the sum result is obviously too small. | Numbers may be saved as text and calculated after conversion using the sorting function or the VALUE function. |
| Boundary condition | Check that null, 0, negative, and duplicate values are handled correctly | Add judgment to these situations, or add a few lines of test data to the exercise table |
Before handing over important data to others, it is recommended that another person or another method check it again. The contents of machine translation and OCR recognition also need to be checked manually, and the formula calculation is the same, so don't look at the results once and use them directly.
# Core comparison: how to match the three learning resources
When self-study, videos, picture and text tutorials and exercise files have their own usefulness, and the effect of using only one is often limited.
| Learning resources | What is suitable for doing? | What should I pay attention to? |
|---|---|---|
| Video explanation | Look at the operation process and understand the input order and mouse click position of the function. | It's easy to read and understand, stop and forget, and you must do it at the same time. |
| Picture and text course | Check the grammar and the parameter description, which is suitable for turning back when you encounter a problem. | Lack of dynamic demonstration, not suitable for getting started from scratch |
| Exercise file | Verify whether you can really use it and expose loopholes in your understanding. | If you want to choose the data that is close to the real job, it doesn't make sense if the practice data is too simple. |
A relatively safe way to cooperate is to first learn a function with video, then check the parameters with graphics and text tutorials, and finally complete a small task independently with exercise files.
# frequently asked questions (FAQ)
1. Without any foundation, can I start with the advanced functions of the complete video?
It's not recommended. You should first look at the introduction to cells, formula input, and relative and fixed references, and then enter the function classification. Skip the basics and go straight to advanced functions, and it's hard to tell what the problem is when you encounter an error.
2. What if the function in the video can not be found in my software?
First confirm the software version, and then check that the function name is spelled correctly. If the version does not support it, you can replace it with the existing combination of the old version, such as the combination of INDEX and MATCH instead of the newer lookup function, and the result is the same logic.
3. Function has learned, why does it go wrong when using a real table?
It is common for real tables to merge cells, text numbers, extra spaces and blank lines, which will affect the calculation results. Before formally applying the formula, check the data according to the previous checklist before deciding whether the formula needs to be adjusted.
4. Learn in free video, how to judge whether a set of courses is worth watching?
You can look at three points: whether there is an exercise file, whether it is divided into chapters according to the function, easy to review, and whether the comments section discusses specific operational issues. In addition, do not upload forms containing personal information to websites of unknown sources for exercises or conversion.
# Summary
Self-study Excel function, instead of pursuing a large number of "excel function tutorial video complete self-learning", it is better to learn and practice with the data prepared by yourself in the order of "single function, combination of conditions, finding matching, and nesting". After learning a batch, check the results in another way, so that the functions you have learned can really be used in the worksheet.
