Professor Excel
table, of, contents, excel

Table of Contents in Excel: 4 Ways to Create a Directory

Sometimes Excel workbooks become quite large: The more worksheets there are, the more difficult it is to keep the overview. A table of contents might help. In this article we’ll explore 4 ways of creating tables of contents in an Excel workbook.

[…]

Copy Exact Formulas: 4 Ways of Preserving the Cell Links in Excel

thumbnail, copy, paste, exact, ranges, exact ranges, persist, preserveThis problem occurs quite often: Excel always adapts the cell ranges in formulas when copying cells. An example: Cell A1 links to A2. When you copy A1 to B1, the link will change to B2. This example is comparatively simple but there are more complex situations. In this article, we’ll take a look at four methods for copying and pasting the exact formulas in Excel: Two manual ways by using the $-sign or copying the cell contents, the third method by using a simple replace trick and the fourth: the automatic method.

[…]

break, links, hyperlinks, pivot, data validation, remove, conditional, formatting, excel

Break Links in Excel – All of Them (Even When Excel Doesn’t)

When you copy cells or worksheets from another Excel workbook, links to other worksheets in many cases still persists. Excel offers a function to break links but this function only works with links within formulas. There are many other types of links as links within conditional formatting rules or data validation rules. The bad news: Those links can’t be cut easily. The good news: there are still ways to break these links.

[…]

excel, paste, tranpose, link, cells

Transpose and Link Data to Source in Excel

When you copy and paste cells in Excel, you can either paste them as links or transpose them. Excel doesn’t allow doing both at the same time. Unfortunately, you often need to link and transpose. But there are three ways for accomplishing this: Doing it manually, using the array formula {=TRANSPOSE()} or Professor Excel Tools. 

[…]

How to Create Drop Down Lists for Excel Cells

You are creating an Excel sheet, in which another user has to enter text data (for example, the name of a person or a place). Such texts can be misspelled easily. To prevent misspelling, you can provide drop-down lists, from which the user can select. The user can also simply type text, but the text has to match exactly one of the items on the list.

How to Create Charts in Excel

Once your calculations are done, you might want to show your results in charts. Charts are a good way of getting a feeling of the values and leading to conclusions.

How to Repeat Rows and Columns on Printouts

When you print a large table, you can select rows or columns which you want to repeat on each page. This way, the data is easier to read as the heading will be printed on each page.

data, table, data table, excel, convert, normal range

Data Table to Normal Range: How to Convert an Excel Table

Data Tables were introduced in Excel 2007. They were supposed to simplify the work with data in Excel. But in the reality they often made it more difficult. For example, handling different formulas in the same column is not really possible. Fortunately, you can easily convert a Data Table into a normal range.

advanced, filters, excel

How to Use Advanced Filters in Excel

Besides the normal Filter, there is a advanced Filter in Excel. What does the advanced Filter do?

Separate Text Into Columns in Excel

You have copied data from a large table on a webpage and pasted it in Excel, but the data is only shown in one column instead of a table? Or you simply want to separate names at every space character? These (and many more) problems could be solved with the “Text to Columns” function.