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.
Category: Excel Features
In this category you find all articles about Excel features, such as “Remove Duplicates“, “Filter” etc.
Keep References: How to Copy & Paste Exact Same Formulas!
This 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… Continue reading Keep References: How to Copy & Paste Exact Same Formulas!
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:… Continue reading Break Links in Excel – All of Them (Even When Excel Doesn’t)
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 Insert a Drop Down Lists in Excel Cells With Just a Few Clicks
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 Easily Insert 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 as Headers
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 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.
How to Use Advanced Filters in Excel
Besides the normal Filter, there is a advanced Filter in Excel. What does the advanced Filter do?
Text to Columns: How to 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.