Excel Tutorial and Tips for Spreadsheet Analysts
Excel TV
Share The LOVE

Category Archives for Excel Tips & Tricks

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

How To Make Radio Buttons Larger With Excel VBA

Microsoft Excel allows you to create the form with radio buttons, text boxes, check boxes and everything needs to complete the form. Here’s the problem though, form elements in Excel are small and you can’t make them larger. Even if you try to make them larger you are not lucky enough as it does not […]

Continue reading

Change Text Color & Font In Excel – “How To” Tutorial

Have you ever thought to change text color and font in Excel? Till now, we are only aware of how to change color and font for the entire cell contents. Yes, right? But, sometimes you would be needed to change text color and font. I mean changing the color and text to little piece of […]

Continue reading

Query Active Worksheet – Excel Power Query Tips

Let’s say we just got handed a report in Excel and have been asked to calculate various things from it. And the report looks something like the image below. Now, there are two ways to go about it: Use VLOOKUP, INDEX, MATCH and IF statements to calculate all the statistics we need. Convert the sheet […]

Continue reading

Sum Multiple Columns With Excel Macro – Excel VBA Tips

In high-pressured meetings with Chief Executives, no one would want to be seen as clumsy or slow. And we, as data analysts, are assumed to be fast with navigating spreadsheets or calculating various stats. It is certainly not a good choice to let anxiety take the best of us in front if the C-suite. But, […]

Continue reading

Using Named Formulas In Excel – Like Yesterday()

What if there is a way to define new formulas in Excel without using VBA? One would be very skeptical of any such claims. But, believe it or not, it is true. Think about how easy such a technique would make your life. Not only would it allow you save a lot of time, the […]

Continue reading