Professor Excel

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?

How to Paste Links to Source Cells Instead of Values

You finished your calculations and now you are about to present your results? Well built Excel models usually separate the calculations from the results. Therefore “Paste as Link” might be helpful for you, especially when your colleagues should not mess your data source.

Buttons in an Excel Cell: How to Add a Form Control

You are looking for a simply way to make your Excel table look professional? Hardly known but easy to use: Buttons, for example, Check Boxes or Spin Buttons can change values. Before you start using buttons, you have to display the Developer tools. Right-click on any ribbon and click “Customize the Ribbon”. Make sure the box for Developer is ticked on the right hand side.

How to Set Headers and Footers for Printouts

Formatting an Excel table for printing is usually troublesome. You have to adapt the print area, select rows and columns to repeat and set the header and footer. In this Excel Tip, we’ll have a look at setting headers and footers.

data, table, excel, what-if

Data Table: Analyze Your Results in Excel

An advanced method of analyzing the results of your calculations is to use a Data Table. With the Data Table, Excel can do your calculation again by changing two input values.