You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP.
Knowledge Area
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)
7 Simple Tricks to Make An Excel Workbook Look Professional
Creating Excel workbooks is often a long process: Setting up the structure, importing inputs, conducting the calculations and eventually tidying it up and sharing it. So once you are done organizing the contents, you have to make sure that the contents are delivered and received well. Therefore it’s crucial that the workbook shows a certain… Continue reading 7 Simple Tricks to Make An Excel Workbook Look Professional
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.
Wrong Calculations: Why Does Excel Show Wrong Results & How to Fix It
Excel calculates wrong. Yes, in some cases, Excel will return wrong results. You don’t believe me? Then type the following formula into an empty Excel cell: =1*(0.5-0.4-0.1). The result should be 0. But what does Excel show? -2,77556E-17. This is just a simple example, but when it comes to larger Excel models it can be quite… Continue reading Wrong Calculations: Why Does Excel Show Wrong Results & How to Fix It
Combine Two Chart Types in Excel: How to Create Combo-Charts?
Creating charts in Excel is quite easy: Select the data and choose your desired chart type on the ‘Insert’ ribbon. But when it comes to combining two chart types – for example a column chart with a line on top – many users suddenly struggle. But actually, it’s almost as simple as inserting a normal… Continue reading Combine Two Chart Types in Excel: How to Create Combo-Charts?
NOW: Learn the Secrets of the Simple NOW() Formula in Excel
The NOW formula returns the current date and time. It can be applied easily by just typing =NOW()
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 Work on Several Excel Worksheets Simultaneously
Sometimes you want to do changes to several worksheets at the same time. That requires, that all the worksheets have the same structure. So, how to do that?