10 things most people do not know about Microsoft Excel
Ankush Chopra Categories: Your Dose of Excel
Ankush Chopra
FOUNDER, Qling
Share
Key Takeaways
- You can always discover new and more creative ways to get more out of Excel
- You can write formulas faster and free-of-errors using dynamic arrays
- The new formulas in Excel 365 have significantly reduced the complexity in solving business problems
Microsoft Excel is arguably one of the most frequently used apps by working professionals. People use it beyond number crunching. For many, it is the go-to app for simple calculations, preparing lists, creating a plan, or even taking notes.
Excel has so much to offer that every now and then, I discover new and more creative ways to use it. With the release of Excel 365, Microsoft has made it even more powerful.
While there is so much to offer, I see many people do not get the most out of Excel. In this blog, I am sharing my top 10 ways in which I like to use Microsoft Excel and achieve more.
You can always discover new and more creative ways to get more out of Excel
#1 Write Formulas Faster Using Dynamic Arrays
I usually work with a large number of rows in Excel. It can be very time-consuming to write and review formulas for each of the columns and then copying them across all cells. There is also the risk of not fixing the row or column referencing correctly.
Dynamic arrays take care of this. You can select all the rows or columns against which you need to write the formula and write it only once for the first cell. And you don't have to worry about referencing or copying it. Excel takes care of it. Microsoft has simplified dynamic arrays considerably in Excel 365.
Once you use dynamic arrays, you will never go back to the older way of writing formulas.
You can write formulas faster and free-of-errors using dynamic arrays
#2 Evaluate Formulas to See How a Formula is Solved
Sometimes you get a complex formula wrong. It's challenging to find the source of error in case the formula has nested functions within it.
Excel provides a utility called Evaluate Formulas (only in Windows), which helps you see how a (complex) formula calculates and arrives at a particular output. This tool helps you quickly identify the source of the problem and fix it.
#3 Creating Gantt Charts
I have worked on large projects for many years now. One of the most critical elements of a large project is an up-to-date project plan and Gantt chart. It used to be a pain updating them every time the "planned dates" (topic for another discussion) were revised in a review meeting. It can be very time-consuming and tedious to color the cells, representing a timeline over 15-18 months.
However, you can update a Gantt chart automatically using conditional formatting. This not only reduces the pain of manually updating the chart but also eliminates chances of errors in updating them.
#4 Using In-built Summary Information
I usually work with raw data and use pivot tables and charts to present my analysis. These two tools automatically provide summary information like sum, average, percentages, etc. without having you calculate it. This saves so much time, as this data is readily available and you can use it in your analysis easily.
#5 Transpose Formula
I use Excel for automation heavily (you can read an interesting story here). At times, formula writing becomes easy if a particular set of data is available in rows instead of columns. While you are working on the sheet manually, you can always use the Paste as Transpose option, but you may have to write a more complex formula if you need to transpose data in an automated sequence.
The Transpose formula (introduced in MS Excel 365) is underutilized and can achieve the same result as Paste as Transpose using a formula. It has made my life simpler on many occasions!
#6 Dependent Lists with Spaces in the Name
It is easy to create a list or a dropdown using Excel. However, very few people know how to create a dependent list (e.g. the state list gets populated based on the country selected by you). It becomes even more difficult if there is a space in the parent list item (e.g. United States of America as the country name).
Creating such lists helps you maintain the integrity of the data and have consistency, which helps in quicker and more accurate analysis. Using a clever mix of format-as-table, names, and index-match formulas, you can easily create dependent lists with spaces.
#7 The X factor in XLOOKUP
As I have mentioned before, I use Excel extensively for automation. Naturally, I used the VLOOKUP formula a lot for all the automation work.
There are many limitations with VLOOKUP, but these two are critical. First, VLOOKUP does not search for data to the left of the known value. Second, it only searches the data from top to bottom.
With the XLOOKUP formula, both these limitations are gone. This makes it easier to automate your workflows. If you have not used this formula yet, then drop everything and learn how to use this formula today.
The new formulas in Excel 365 have significantly reduced the complexity in solving business problems
#9 Sort on a Custom Criteria
The first time I tried to sort data on months, I thought I had found a bug in Excel. The data got sorted as April, December, February, and so on. I figured Excel was sorting the data alphabetically, and it was not a bug and also not my moment of glory!
I soon discovered that I could sort the months using the sort order as 'Custom List'. This also allowed me to define a custom sort order and organize data accordingly. This is extremely useful when sorting data for a project plan.
#10 Deciding on the Right Loan Provider
Whether you are evaluating a loan for an organization or your personal needs, a critical factor for decision-making is the amount of monthly payment (PMT or EMI). The monthly payment depends on the tenure and interest rate. The lending agency or the bank usually provides the monthly amount for a fixed period based on the interest rate they offer.
However, for our decision-making, we would ideally like to see multiple combinations of tenure and interest rates, which are not available with any lending agency or bank. You can generate this data easily using Excel's data tables and use it for more informed decision-making.
Wrap-up
These are 10 ways in which I extract a bit more from Excel every day. You can also read my blog on how I started teaching Excel.
To share my learning with everyone, I have created a course, Excel Masterclass where you could learn all this and much more. It is a course, which can help you speed up your productivity rapidly and give you skills to conduct meaningful data analysis and create engaging and interactive dashboards. You can learn more about the course by clicking here.
Related Features
Related Blogs
How I started teaching Excel?
- Story of how an innocuous call led to a teaching assignment
- Teaching is the best way to learn more about any subject
- You get the most joy by making the experience relevant for your learners
Three common mistakes to avoid while working with Excel
- With systematic learning, nothing can stop you from using Excel with ease over time
- Most people teach how to use formulas in Excel. However people want to learn Excel to solve business problems
- Young professionals can benefit tremendously in their careers by mastering the skill of analyzing data in Excel
How a young employee achieved more with Excel?
- Limited understanding of Excel can prevent you from getting credible insights from data
- To be successful, you need to learn analysis and not the top 5 formulas
- Excel is an easily available tool that lets you find answers to business problem quickly
Thinking about taking our Excel Masterclass?
- Deciding which Excel course to take can be hard
- There are 5 important factors a working professional must consider while selecting an Excel course
- Good Excel skills enable you to add more value to your analysis, irrespective of your niche
0px
Want updates? Let us know.
If you would liked to be notified when we release a new blog or have anything worthwhile to share, then please subscribe to our newsletter. We won't spam you. You can unsubscribe anytime.
I have read and agree to the terms & conditions.
