Excel Tutorial and Tips for Spreadsheet Analysts

Category Archives for Excel Tips & Tricks

How to Change Excel Serial Date Format Using Text to Columns

Microsoft Excel allows us to show any complex data in an easy way. You could only see some format changes when you extract the data from the database. For example, say you have the data related to employee related to joining date. Then, when you extract this data from the database, then you would see […]

Continue reading

This Excel Keyboard Shortcut Hack – Save 10 Hrs Every Week

Imagine a situation where in you applied some border, changed font color, did something with alignment, added a background color or even added some formulas to that cell or group of cell, How to get rid of every thing that you applied to that cell. One thing which 90% of the People would do is […]

Continue reading

Customize Excel Ribbon – Change Case of Text

We all know that Excel Ribbon has tabs like Home, Data, review, Insert and many more. This allows us to perform some tasks like formatting the text, inserting bullets and more. Without this Excel ribbon nothing can be done. But, if you observe it, text on all tabs of ribbon is in camel case. Do you […]

Continue reading

NPV Calculator and IRR Formula in Excel

Excel makes it possible for simple buy / not-buy or do / not-do financial decisions very easy to arrive at. The underpinning concept behind making such decisions is that a dollar tomorrow is worth lesser than a dollar today. That’s right, it’s because of the additional return you can get by “investing” a dollar today. […]

Continue reading

Command + T To Cycle Through Excel Cell References On A Mac

As frequent Excel users, we know the importance of absolute and relative references. On Windows operating system, one can use the F4 key to turn cycle through absolute and relative references, and everything in between. But there are a lot of users who use Excel on MAC and are clueless about how to access the […]

Continue reading

Data Parsing in Excel Using LEN and SUBSTITUTE function – HOT Tips

Analyzing and parsing data in Excel is really a tedious task. If it is not done smartly, then we end up in doing lot of mess. For example, say you have list of email addresses in Excel and you want to extract the extensions of those email addresses. Most of us think of using Text […]

Continue reading

Data Validation with Wingdings

Did you know you can indicate whether the user of your file is putting in the right information or not? And that too without using ‘Data Validation’ tool or ‘Conditional Formatting’? Sounds unbelievable, but it could definitely be done. And the advantages are straight-forward as well. Firstly, you do not have to use volatile conditional […]

Continue reading

Relationships Between Linked Tables in Excel

Microsoft launched PowerPivot as a separate add-in for Excel 2010. And since then it has become an integral part of any data analyst’s toolkit. The immense “power” of this tool comes from the fact that it can aggregate data from related but distinct tables into one pivot table. Don’t worry if you’re slightly confused, Oz […]

Continue reading

Creating Random Data in Excel using RANDBETWEEN and CHOOSE

Many times we are in need of random data. Suppose, if you are doing some tutorial on Excel formulas, then we need to have random data in your Excel sheet. There are many situations in the same way and when we are in need of some random data, we generally use some websites that helps […]

Continue reading

Excel Tips – Tables and Structured References

Excel Tables arranges data in a structured way in rows and columns. When you have data as output of some analysis then showing it in Excel Table is my suggestion. It makes us easy to work on that data to further process our analysis. In this tutorial Excel expert Oz explains us how to use […]

Continue reading

Index Function In Excel To Move Columnar Lists To A Grid

Sometimes we have analyze or sort through data all of which is in a single column. The problem occurs when not every entry in that column represents the same type of data. For example, you might have names, addresses, email IDs and so on, all in just one column. It is really difficult to analyze […]

Continue reading

CTRL + SHIFT Excel Shortcut – Drag Copy of Shape Along A Grid

Have you ever faced problems while making copy of buttons in Excel? We normally do so by dragging the button with the CTRL button being pressed. But, you might have seen that dragging by holding the CTRL button, does not allows you to move on the plane or line you want. To be frank it […]

Continue reading

Tackling Complex Formulas in Excel

When dealing with a small but complex set of calculations, it is very tempting for many of us to put long, complex formulas in a few cells. Or sometimes, as Excel experts, we would be asked to find calculation errors in such situations. Of course, who wouldn’t like to have neat-looking workbooks? But there is […]

Continue reading

Top 5 Quick Time-Saving Excel Shortcuts

In this universe of crowded excel shortcuts, let me share with you five less-known shortcut keys of Excel which I believe a lot of you wouldn’t have used till now. I have handpicked these based on the use case from my interaction with 2,000+ classroom participants. I’m sure it will help you. List of Shortcuts […]

Continue reading

How To Make A Calendar Table With Power Query – Excel Tips

People who work with PowerPivot a lot know the importance of dates to analytics. If our data does not have a calendar or a date column, it will be very hard to get to calendar intelligence or derive time-based insights. Now, there are various ways to go about doing this. But the best one is […]

Continue reading
>