You opened an Excel file and it immediately showed an error message saying that there is a circular reference. What to do now?
Let’s assume the following situation: You receive an Excel file from your colleague and have to understand it as quickly as possible. How do you start?
The light grey gridlines are quite useful. They provide a quick impression of the rows and columns of your Excel worksheet. But once you want to create your final layout, they look annoying. Your worksheet looks more professional with hidden gridlines.
Working with other people on the same Excel file can be challenging. Especially when you prepare an Excel workbook and another person has to fill in some data. In such case, you probably want to make sure that only your intended values are possible to enter.
Have you ever seen these tiny graphs within cells and want to use them too? They are called Sparklines and inserting them is quite simple:
You want to get data from different sheets but always on the same cell? INDIRECT returns the value of a cell which you specify by a string. For example, if you write =INDIRECT(“B2”) it’ll give you the value of B2. Instead of just B2, you can also refer to other sheets, for example =INDIRECT(“Sheet1!B2”) or even other workbooks.
Sometimes, you want to create random values in Excel. There is a simple formula to generate random numbers: RAND. If you type =RAND() into a cell, you’ll get a number between 0 and 1. So what do you do, if you want to have random number between 20 and 50 for example?
You’ve done some calculations in Excel but you aren’t satisfied with the result? So you ask yourself, how you could achieve the desired result. A good starting point for such analysis is the built-in Goal Seek function in Excel. It’s simple to use but yet powerful. On the other side, it does some reverse calculation and therefore has a reputation for cheating in order to achieve the target results.
Dealing with text in Excel can be painful as the formatting is limited within Excel cells. One method of organizing text is to add line breaks.
The “Conditional Formatting” button is hidden in the middle of the Home ribbon in Excel. Nonetheless, Conditional Formatting offers many options for visualizing your data. As the name already says, Excel can format your table depending on one or more conditions.