Let’s see how to shuffle the records of a dataset using the RAND, LARGE, ROWS and VLOOKUP functions in Excel. Step 1: Insert a column on the left side of the dataset and generate an array of random numbers in it. The RAND Function in Excel can be used for this purpose. =RAND() Step 2:…
Following are 4 different methods in Excel to find the ‘Day Name’ corresponding to a ‘Date’. 1. TEXT Function to find Day Name from Date The TEXT function in Excel can be used to return the corresponding Day Name, Month or Year from a Date. The following formula will return Tuesday, the Day name corresponding to the…
What is a Linked Picture in Excel? In Excel, a Linked picture is a live image of a cell or a range of cells in a worksheet. In other words, dynamic snapshot of particular data range in a worksheet. When working with many worksheets of a Workbook that are connected with formulas, we can use…
There are instances in which leading zeroes need to be added to a dataset in Excel, such as when maintaining Transactions IDs, Employee IDs, Zip Codes, Social Security Numbers, etc., In this blog post, we will see the different ways in which we can pad numbers with zeros at the beginning. TEXT Function in Excel…
Following are 4 different methods that can be used to Jump to the First or Last sheet of an Excel Workbook. Method 1 Holding the Ctrl key, left-click on the small right arrow present in the bottom left corner of the workbook. You will land on the last worksheet of the active workbook. Similarly, to select the…
In Microsoft Excel, we can set the Transparency level for pictures inserted into it. The following are the two methods to change the transparency of a picture in Excel. Method 1 Select the picture > Go to the Picture Format tab > In the group called Adjust, click on the drop-down menu for Transparency Make a picture transparent in Excel 7 Transparency levels…
3 different methods to display formulas used in an Excel worksheet. Method 1 Go to the Formulas tab in the Excel ribbon, Click on Show Formulas Once you click on this button, every cell in the worksheet will display the formula used in that cell, instead of the resulting value. To turn off of this…
Excel supports Hijri and Umm-Al-Qura dates. This means we can display a Gregorian date in Hijri just by changing the Number Formatting of the corresponding cell. To display the Hijri equivalent of a Gregorian date, Right-click on the cell containing date > Format Cells > In the Format cells dialog, select Date from the options under Category…
Suppose, you want to restrict the number of characters that can be entered into an Excel cell. How will you solve this problem? The answer is Data Validation! Data Validation in Excel can be used to control the range, type & length of the data that can be entered into a cell. This blog post…
This blog post is about 2 Excel formulas that can be used to Extract positive and negative numbers in Excel. Using MAX and MIN functions The Excel formula =MAX(B3,0) will return the number in cell B3 only if that value is positive. Similarly, =MIN(B3,0) will return the value in cell B3 only if that value is negative. Note:…