Learn Advanced Excel with Free Lessons & Tips

How to convert the absolute numbers in pivot table into Lakhs, Millions, Trillions without adding a spare column in the data source.

Name the field in the Calculated field and apply the formula by Double-clicking on the field representing the numbers (available below).

Example :

Name: Price(in Mn)

Formula := 'Total price' / 10^6  ---> to covert into millions.

Now here comes the new field with Price(in Mn) in the pivot table. Now we can remove or add it later as per our requirement

In this video, learn about Advanced Excel:

- Data Input and Hidden Features,

- Worksheet Navigation,

- Excel Formulas & Functions,

- Data Cleaning and Formatting.

VBA and its useful Built-in Functions, Debugging, Error Handling & Procedure Scope and more.

Yes, it is possible to set multiple conditional formats and also to even have them active at the same time. So if one formatting only specifies a text colour and the other only a background, then you can get both effects in a cell. The very tricky thing is that once you set it, you will not be able to unset formatting, so you need to switch to the " Red Border" that is preset before actually adjusting your pre-determined desired format.

 For example, there is a table that contains the name (Column A) and subject wise marks (Columns B to G) of students of a class. Say the class has 50 students. Row 1 will heave headers so that data will be populated in Rows 2 to 51.

One level of conditional formatting that can be applied to cells B2 to G51 is that if the value of the cell is greater or equal to 35, the cell must turn green. The second level of conditional formatting can be set as if the marks of that subject are more significant than the class average; then the font must turn Bold.

Hence, in the first case, a cell is a part of range A2: G51, and in the second case it is a part of column B, C, D, E, F or G. Depending on the value, a cell can become both Green as well as BOLD.

There is one more thing. You can select the option of stopping further rules to be applied if the first rule is run TRUE.

 Hope this helps

Excel and Access are two Microsoft applications. The foremost difference between Excel and Access is that Excel is a spreadsheet to perform calculations and to interpret data visually. At the same time, Access is a Database Management System that helps store and manage data efficiently. 

Access is better for managing data: helping you keep it organized, easy to search, and available to multiple simultaneous users. Excel is generally better for analyzing data: performing complex calculations, exploring possible outcomes, and producing high-quality charts. The purpose of Access is to assist in collecting, sorting, and manipulating the databases. Compared to Access, the storage capacity is less since excel isn't built for storing data.

Advanced Excel concepts are not the extension of Excel concepts, as the name may suggest. It covers the data side of of the Excel concepts such as VLOOKUP, MATCH, INDEX, Pivot tables etc. These concepts are about how to apply the Excel concepts and basics in an application or to make your job easier.  

Other subjects get covered quickly and efficiently according to your grasping power like recording and looking at macros, conditional formatting, testing and auditing formulae, advanced charts and concepts.

In a world of self-made tutors, one way to be unique and stand out is to make sure that you're teaching in the students in the right direction.

1. Understand what concept the student needs more concentration on.
2. Give personal attention to every student. Its a little challenging, but pays off big time.
3. Encourage feedback and suggestions. 
4. At the end of each concept, make sure you summarize so that the students don't lose their continuity.
5. Since you're recording your session, make sure you're loud, clear and audible with good lighting. A good video or audio clarity will attract the students.
6. You can add subtitles, in case some don't catch up with your speed. 

These are some of the things that will help you stand out from other tutors.

The duration to complete any kind, of course, depends on your grasping power and the ability to learn the concepts quickly. Having said that, it usually takes about three to four weeks to learn the topics in Advanced excel. For this, you need to be through with your MS Excel concepts and also practice every day.

Advanced excel contains topics like VLOOKUP, Macros etc. These topics are pretty time consuming but easy to learn. So, it all depends on you. If you practice every day and dedicate around 2-3 hours every day to learn the concepts, then you can learn it within four weeks. But, to master the concepts in Excel, you need to use the tricks and formulas on a daily basis.