Professor Excel

Clear All: How to Also Delete Formatting in Excel

The “normal” way to delete the content of a cell is by pressing “Del” on the keyboard. But what if you want to delete the formatting and comments as well?

How to Paste Values Without Formulas in Excel

When you copy and paste cells “the normal way” (Ctrl + c –> Ctrl + v), formulas and formatting is pasted as well. But in many cases, you want to “freeze” the values, so that they can’t be changed any more.

Column Letters Instead of Numbers in Excel

Showing numbers instead of letters of columns can be useful, for example, when you work with VBA macros or when you have to count columns (e.g. VLOOKUP).

power, pivot, powerpivot, excel, addin

PowerPivot: No More Trouble Working with Big Data in Excel

You got a large amount of data which you want to evaluate in a Pivot Table. In such case, Excel crashes often or gets very slow. You might want to consider using PowerPivot, a free Excel Add-In provided by Microsoft. You can download it from the Microsoft webpage. Once successful installed, you’ll see a new ribbon called “PowerPivot”.

How to Import CSV Files to Excel

There are two ways to import data from CSV files to Excel. Open a CSV file by double-clicking on it or use the import function. The import function has an advantage: You can define the details of the import, for instance, set the meaning of thousand separators.

How to Avoid Changing the Column Width With Each Pivot Table Update

In a previous Excel Tip we have learned how to create a simple Excel Pivot Table. Now we will go on from there and learn how to eliminate one of the major pains of Pivot Tables: It changes the size of the columns after each update of the values.

Filters: How to Use Filters in Excel

You received a large Excel table, possibly as output of a database system. The first approach when working with data in Excel is often the filter function, previously called “Autofilter”.

How to Solve Errors in Excel

A troublesome topic when it comes to creating large Excel models is Error Checking. Excel offers an Error Checking function in the Formulas ribbon which let’s you jump to the next error. Error in this case are all the cells showing an Excel error message as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

LEFT or RIGHT: Extract Text from Cells in Excel

LEFT and RIGHT are string formulas for working on text. You use them when you want to extract some parts of a text. LEFT returns a number of characters of a cell, starting from the left hand side. RIGHT does exactly the same, but starts from the right hand side.

How to Freeze the Headings

Your worksheet is larger than your screen? That means when you scroll up and down, the header row of your data is invisible?