Excel Tips and Tricks
How to return an entire row
To return an entire row you need to use array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET. How to...
How to return an entire column
To return an entire column you need array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET. How to return...
How to convert a URL into a hyperlink automatically
Excel can detect URLs and convert them into hyperlinks when you press the Enter key on that particular cell. When working with a large data set with thousands of rows you may not want to go to each cell and repeat this process. Enter the HYPERLINK...
How to Transpose The Rows and Columns of a Data Table
Are you tired of manually transposing rows and columns in Excel? Switching between rows and columns can be tedious, especially with frequently updated data. However, there's a solution: you can efficiently transpose rows and columns in Excel using a single formula....
Manage Tasks Better by Visualizing the Data in Gantt Chart
What is a Gantt Chart? Gantt charts are widely used in managing resources, operations, production, and other fields. The start and end times of each item is portrayed using the X axis scale and the bar length. Thanks to the way this chart is structured, you can see...
How to create a random secure password in Excel
Managing and coming up with strong passwords can be challenging. Let us show you how you can create your own custom password generator in Excel. You can download our sample workbook below. Syntax Random Character Generation =RANDBETWEEN(0,9) (Numbers)...
How to shuffle a list of items in a random order
Do you have a list of items or names that you want to sort randomly? Using the RANDBETWEEN, RANK, and INDEX functions in conjunction, you can easily shuffle an item group for a random draw. Syntax =RANDBETWEEN( 1, number of list items ) + ROW() / very...
How to add comments into formulas
In complex models, it is important to write comments explaining how the formulas work. But the limitation of Excel's Insert Comment feature is that you must type the entire comment in a textbox. There is an alternative. You can use N() formula to insert...
How to create a calendar that only contain workdays
Be it for creating a time card or planning, creating a flexible calendar where you can omit holidays will come in very handy. Using the WORKDAY function you can easily create such a table and incorporate it in your data models. Syntax =WORKDAY(previous...