Forem: Excel24x7

How Can You Assign a Value/Category based on Number Range in Excel?

Vigneshwaran Vijayakumar — Tue, 17 Jun 2025 08:23:00 +0000

Assigning a category based on a number range is a common practice everywhere in schools, colleges, and offices. You can do this in Excel, too. By using the right Excel functions and formulas, you can assign categories to values based on their range in the cells. In this tutorial, I will share you the three working methods to Assign a category or value based on the number range in Excel.

The functions used in this tutorials are,

Functions Used	Used for
IF	To perform logical tests and return different values based on whether the test is TRUE or FALSE.
VLOOKUP	To look up a value in the first column of a table and return a value in the same row from another column.
IFS (Later 2019 or Office 365)	To test multiple conditions and return a value corresponding to the first TRUE condition.

Assigning Category to the Number Range Using IF Function:

The IF function in Excel helps you perform a logical test and return one or more values depending on whether the test is true or false.

Let’s say we have a table with a list of student names and their total marks. Now, we’re going to use the IF function to categorize their performance into three categories such as Poor, Fair, or Good based on their total marks.

Using the IF function to assign performance categories to students based on their total marks.

The used formula is,

=IF(C4<=200, "Poor", IF(C4<=400, "Fair", IF(C4<=600, "Good", "Excellent")))

If the input value is 200 or less, it shows “Poor“.
If it’s more than 200 but 400 or less, it shows “Fair“.
If it’s more than 400 but 600 or less, it shows “Good“.
If it’s more than 600, it shows “Excellent“.

Note: This method is suitable for the short conditions and small datasets Excel worksheets.

Assigning Category to the Number Range Using VLOOKUP Function:

Using the VLOOKUP function for this task is a professional approach. First, you need to create a helper table like the one shown in the image below. The second table should contain the mark ranges and their corresponding performance categories.

Create helper table for assigning the category using the VLOOKUP Function.

Now, I’m going to use the Excel formula to assign the performance category based on the total marks of the students using VLOOKUP Function.

 =VLOOKUP(C4,$F$4:$G$7,2)

C4: This is the cell containing the value you want to look up.
$F$4:$G$7: This is the range of cells where the VLOOKUP will search for the value. It’s “locked” with dollar signs so it doesn’t change if you copy the formula.
2: This tells Excel to return the value from the second column of the lookup range.

Executing the above formula will instantly assigned the performance category to the students based on the marks with the help from the helper table.

Using the VLOOKUP function to assign performance categories to students based on their total marks.

That’s it, This is how you can use the VLOOKUP Function to assign categories based on the inputs in Excel.

Assigning Category to the Number Range Using IFS Function:

Using the IFS function to do this will remove the need to repeat the function, like we did with the IF function above.

The formula used here is,

=IFS(C4<=200,"Poor",C4<=400,"Fair",C4<=600,"Good",C4>600,"Excellent")

Using the IFS function to assign performance categories to students based on their total marks.

Difference B/W IF & IFS Function:

IF Function	IFS Function
Checks one condition at a time.	Checks multiple conditions in a single formula.
To check multiple conditions, nested IFs are needed, which can get complex.	No need for nesting, easier to read and write.
Available in all versions of Excel.	Available only in Excel 2016 and later.
Can return a value if TRUE and another if FALSE.	Can return different results for each condition.
Harder to manage when there are many conditions (nested IFs).	Easier to manage when there are many conditions.
More flexible in certain cases when you need only one condition.	More efficient for evaluating multiple conditions simultaneously.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How Do I Find Cell Contains Maximum or Minimum Values in Excel?

Vigneshwaran Vijayakumar — Sun, 15 Jun 2025 18:24:00 +0000

Finding the maximum or minimum values in Excel is important when you’re working with a lot of data. Luckily, you can use formulas to find the cells that contain these maximum and minimum values. In this tutorial, I will show you the most effective ways to find the cell addresses that have both the maximum and minimum values in rows and columns.

Find Cell Address from a Column of Numbers:

Let’s look at a worksheet with sales values in column B, from row 4 to row 18. These values are random and can change. Please refer to the image for more details.

Random values in the Row

Formula to Find the Cell Address having Maximum Value from Rows:

=CELL("address",INDEX(B4:B18,MATCH(MAX(B4:B18),B4:B18,0)))

MAX(B4:B18) – Finds the highest (maximum) value in the range B4:B18.
MATCH(MAX(B4:B18), B4:B18, 0) :
- MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18.
- The 0 in the MATCH function means it looks for an exact match.
- MATCH returns the relative position of that maximum value in the range.
- For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7.
INDEX(B4:B18, 7) – It returns a reference to the 7th cell in the range B4:B18 (which is B10).
CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value.

Copy & paste the above formula to find the cell address containing the maximum value. The formula will returns the result as “$B$8“. The value $B$8 indicates the location of the maximum value in the current worksheet.

Formula to Find the Cell Address having Minimum Value from Rows:

=CELL("address",INDEX(B4:B18,MATCH(MIN(B4:B18),B4:B18,0)))

MIN(B4:B18): It finds the smallest (minimum) value in the range.

The above formula will find the cell address contains the minimum values in the worksheets. The formula will returns the result as “$B$11“. The value $B$11 indicates the location of the minimum value in the current worksheet.

Finding Cell Address contains Both Maximum and Minimum Values from the Rows.

Find Cell Address from a Row of Numbers:

Similarly, the sales values are in the range from column C to column P, all in row 3. Please refer to the image below.

Values are presents in Columns

Formula to Find the Cell Address having Maximum Value from Columns:

=CELL("address",INDEX(C3:P3,MATCH(MAX(C3:P3),C3:P3,0)))

MAX(C3:P3) – Finds the highest (maximum) value in the range C3:P3.
MATCH(MAX(C3:P3), C3:P3, 0) :
- MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3.
- The 0 in the MATCH function means it looks for an exact match.
- MATCH returns the relative position of that maximum value in the range.
- For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3.
INDEX(C3:P3, 3) – It returns a reference to the 3rd cell in the range C3:P3 (which is M3).
CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value.

Executing the above formula will return the cell address value contains the maximum value from the different columns. For example, the formula will return the cell address “$M$3” which contains the maximum value.

Formula to Find the Cell Address having Minimum Value from Columns:

=CELL("address",INDEX(C3:P3,MATCH(MIN(C3:P3),C3:P3,0)))

MIN(C3:P3): It finds the smallest (minimum) value in the range.

Similarly, the formula will returns the cell address contains the minimum value from the multiple columns. For example, the formula will return the cell address “$J$3” which contains the minimum value from the columns.

Finding Cell Address contains Both Maximum and Minimum Values from the Column.

Functions Used to Achieve this:

Used Functions	Functions Used for
CELL	Returns information about the formatting, location, or contents of a cell.
INDEX	Returns the value of an element in a table or array, selected by row and column number.
MATCH	Searches for a specified item in a range of cells and returns the relative position of that item.
MIN	Returns the smallest number in a set of values.
MAX	Returns the largest number in a set of values.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How Can I Delete All AutoShapes, Objects in Excel?

Vigneshwaran Vijayakumar — Fri, 13 Jun 2025 08:18:00 +0000

AutoShapes in Excel refer to predefined drawing shapes that you can insert into a worksheet to improvise visual presentation by adding lines, arrows, rectangles, circles, callouts, flowchart symbols, banners, stars, and more. Objects in Excel is also similar to the AutoShapes, but it extends upto Charts, Shapes or AutoShapes, Pictures, Text Boxes, SmartArt, Buttons or Controls, Embedded Objects, Comments or Notes and Hyperlinks.

Too much objects filled your excel sheets? It is so easy to clear all the objects and autoshapes from your worksheet using GO TO dialog box and VBA Code. In this tutorial, I will guide you to delete all objects and autoshapes instantly from your worksheets in a Excel.

Method 1: Delete Objects, AutoShapes using Go To Dialog Box

Sample Worksheet filled with Objects & AutoShapes.

You can use the Go To dialog box to completely wipe all the images, gif, videos and autoshapes from your worksheets.
You need to use the keyboard shortcut CTRL + G or F5 to launch the GO TO dialog window.

Using F5 to launch the Go To dialog box

In the left bottom, you need to press the option Special…
Then you need to choose the Objects option from the list, and click the OK button to confirm the selection.

Choose Objects and Click OK button.

Once you did this, all the objects in your worksheets will be selected and then you can use the Del keyboard shortcut to remove all the objects from your worksheet.

Using Go To dialog box to select and remove all objects from a worksheets

That’s it.

⚠️This will delete all the objects and autoshapes from your sheets and so make a backup before performing options.

Method 2: Remove Objects, AutoShapes using VBA in Excel

Use the keyboard shortcut ALT + F11 to launch the VBA editor.
Then from the menu bar, you need to choose Insert -> Module.

Choose Module from the Insert Menu

Copy and paste the code in the module.

Sub DeleteShapes()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next Shp
End Sub

Now, You need to use the Keyboard shortcut F5 to execute the command.
A popup window will appear on your screen asking you to choose the MACROS.
Choose the DeleteShapes MACROS and click the RUN button to clear all the autoshapes, objects from your worksheets.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How to Use a Formula to Convert Text to Sentence Case in Excel?

Vigneshwaran Vijayakumar — Wed, 11 Jun 2025 09:15:00 +0000

In Excel, you can easily convert the text string into Sentence Case and it will helps you to read and engageable. While Excel doesn’t have a built-in “sentence case” function, you can achieve this formatting using a custom formula. In this tutorial, I will explain you the multiple methods to convert a text string into a sentence case format in Excel using Formula and VBA codes.

If you want to convert a text string into a valid Capitalized Case and then you can use the Excel PROPER function. The Excel PROPER Function simply converts the text strings into a 1st letter capitalized strings (Each 1st letter of a word will be capitalized in a sentence).

Method 1: Using Formula to Convert Text into Sentence Case:

The below formula converts any text, whether it’s in all capital letters or all lowercase, into sentence case.

=UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)-1))

Formula Converted the input text strings into a Sentence Case Strings

Formula Explanation:

LEFT(B4,1) – This part of the formula extracts the first character of the text in cell B4.
UPPER(LEFT(B4,1)) – This section converts that first character to uppercase.
MID(B4,2,LEN(B4)-1) – This formula, extract the second part of the text string.
LOWER(MID(B4,2,LEN(B4)-1)) – Which converts the text string into lowercase.
& – Simply appends the text strings.

Functions Used	Function Explanation
LEFT	Extracts a specified number of characters from the start (left side) of a text string.
UPPER	Converts all letters in a text string to uppercase.
MID	Returns a specific number of characters from a text string, starting at a specified position.
LEN	Returns the total number of characters in a text string.
LOWER	Converts all letters in a text string to lowercase.

That’s it. This is how you can use the formula to convert any text strings into a sentence case text strings.

Method 2: Using VBA Code to Convert Text into Sentence Case Strings

This is the alternative method to do. You might need to use the VBA code to convert any text string into sentence case strings.

First, you need to use the keyboard shortcut ALT + F11 to launch the VBA window.
Then from the menu bar, you need to choose Insert -> Module.

Choose Module from the Insert Menu

Now, you need to copy & paste the below vba code to convert all text strings into sentence case strings.

Sub SentenceCase()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Sentence Case by Excel24x7.com"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xValue = Rng.Value
    xStart = True
    For i = 1 To VBA.Len(xValue)
        ch = Mid(xValue, i, 1)
        Select Case ch
            Case "."
            xStart = True
            Case "?"
            xStart = True
            Case "a" To "z"
            If xStart Then
                ch = UCase(ch)
                xStart = False
            End If
            Case "A" To "Z"
            If xStart Then
                xStart = False
            Else
                ch = LCase(ch)
            End If
        End Select
        Mid(xValue, i, 1) = ch
    Next
    Rng.Value = xValue
Next
End Sub

Paste the VBA code into the module.

Before executing the code, you need choose the cell range that you want to convert into a sentence case strings.

You need to select the Cell Range

Now, You need to use the Keyboard shortcut F5 to execute the command.
The mini popup window will appear on you screen and in that the selected range value pre-entered by default.

Mini Popup window will appear on your screen

Once you check the selected range values, click the OK button to convert all the cell values into a Sentence case text strings.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How Can You Reverse Signs of Cell Values in Excel?

Vigneshwaran Vijayakumar — Mon, 09 Jun 2025 01:18:00 +0000

Sometimes, when you copy values from websites and paste them into Excel, the sign of the numbers may get reversed due to the way the content is designed in HTML. This issue often happens when copying stock prices or financial data from online sources into Excel. Thankfully, Excel provides several simple and effective methods to fix this. In this tutorial, I’ll explain how to reverse the sign of numbers in Excel using both formulas and VBA code, along with clear examples for better understanding.

Original Cell Values that needed to be Reversed its sign.

You can reverse the sign values of the any cells values using the following methods:

By using Paste Special Function.
By using VBA Codes.

Method 1: Using Paste Special Function to Reverse Cells Values Sign

First, you need to enter the value -1 in an empty cell. In this example, the value -1 is entered in cell G3.

Entering the value -1 in any Empty Cell

Now, you need to copy the cell G3.

Copy the cell G3

After this, you need to select the Range of cell values (B3:E13) that need to be reverse its sign.

Selecting the range of cell values immediately after copying the cell values.

Once you selected the range of cells, you need to right click on the chosen range of cells and choose Paste Special -> Paste Special option.

Choose Paste Special option by right clicking on the selected range of cells.

In the Paste Special dialog box, choose the Multiply from the Operation and then click the OK button.

Choosing Multiply option from Paste Special option

Boom, once you did this, The signs will be reversed in the selected range of cells.

That’s it. This is how you can reverse cell values sign using Paste Special function option.

Method 2: Using VBA Code to Reverse Cell Values Signs

This is the alternative method to do. You might need to use the VBA code to reverse your signs of the cell values. You can use the keyboard shortcut ALT + F11 to launch the VBA Editor in Excel.

Firstly, you need to select the range of cell values that need to be reverse it’s signs (IMPORTANT) as shown in the image.

Select the Range of Cell values

Then, you need to use the the keyboard shortcut ALT + F11 to launch the VBA Code editor.

VBA Editor in Excel

Now, you need to choose the Insert from the Menu bar and choose the Module option as shown in the image below.

Choose Module Option

Now, in the module, you need to paste the VBA Code to reverse the sign of cell values.

Sub Convert()
Dim C As Range
For Each C In Selection
C.Value = -C.Value
Next C
End Sub

Paste the VBA code in the Module

Now, in the Menu bar, you may see the option “Run“, click on it and choose the Run Sub/Userform F5 option to execute the VBA code. Alternatively, you can use the keyboard shortcut F5 to run the command.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How to Sync Selected Range Across All Worksheets in Excel?

Vigneshwaran Vijayakumar — Sat, 07 Jun 2025 06:04:00 +0000

In Excel, you may often work with multiple worksheets or workbooks while managing data. Sometimes, you need to sync data between two or more worksheets. Whether you’re using templates, repeating headers, or shared report values, Excel does not offer a built-in feature to link a selected range across multiple sheets directly.

In this tutorial, I’ve added the two methods including using:

VBA Code to Sync Worksheets.
Sync Worksheets values using Select All Sheets option.

Method 1: Sync Worksheet Values using Select All Sheets Option

Sample Values in Sheet 1 in a Workbook!

Sample Values in Sheet 2 in a Workbook!

Firstly, you need to select a range of cell values in the current active worksheet that you want to select in the all worksheets.

Select the Range of cells that you want to sync with other sheets values

As shown in the above image, I’ve choose the cell values range between A2:B6
Then you need to right click on the worksheet tab and choose the Select All Sheets option from the list of menu.

Choose the Select All Sheets option from the Worksheet tab.

Now, all the values or the selected cell range have been copied and synced across all worksheets in the workbook.

This image displays the range of cell values has been selected across all worksheets in a workbook.

That’s it, this is how you can sync values in a range of cells in the workbook using Select All Sheets option in Excel.

Note: Using the “Select All Sheets” option in Excel lets you apply the same selected range to all worksheets at once. However, this only syncs the selection itself, not the visible position of the selection on the screen. To view the selected range in the same spot on each sheet, you’ll need to manually scroll each worksheet.

Method 2: Sync Worksheet Values using VBA Code:

This is the alternative method to do. You might need to use the VBA code to sync the worksheet values. Using VBA code in Excel, you can easily make all worksheets select the same range and also ensure that the selected range is visible in the same position of the window across all sheets. You can use the keyboard shortcut ALT + F11 to launch the VBA Editor in Excel.

Firstly, you need to select the range of cell values (IMPORTANT) as shown in the image.

Select the Range of cells that you want to sync with other sheets values

Then, you need to use the the keyboard shortcut ALT + F11 to launch the VBA Code editor.

VBA Code Editor

Now, in the Insert option located in the top of the Menu bar, you need to choose the Module option.
A editor window will appear on your screen, in that you need to paste the below code.

Sub SynchSheets()
'Update 20130912
Dim WorkShts As Worksheet
Dim sht As Worksheet
Dim Top As Long
Dim Left As Long
Dim RngAddress As String
Application.ScreenUpdating = False
Set WorkShts = Application.ActiveSheet
Top = Application.ActiveWindow.ScrollRow
Left = Application.ActiveWindow.ScrollColumn
RngAddress = Application.ActiveWindow.RangeSelection.Address
For Each sht In Application.Worksheets
    If sht.Visible Then
        sht.Activate
        sht.Range(RngAddress).Select
        ActiveWindow.ScrollRow = Top
        ActiveWindow.ScrollColumn = Left
    End If
Next sht
WorkShts.Activate
Application.ScreenUpdating = True
End Sub

Now, in the Menu bar, you may see the option “Run“, click on it and choose the Run Sub/Userform F5 option to execute the VBA code. Alternatively, you can use the keyboard shortcut F5 to run the command.

Run VBA Code using F5 Key

Get back to the worksheet, there you can see that the selected range of cells has been synchronized with all the worksheets in the workbook.

Quick Video Tutor of using VBA Code to sync range of cells among all worksheets.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How Can You Convert an Angle Between Degrees and Radians in Excel?

Vigneshwaran Vijayakumar — Fri, 23 May 2025 15:12:00 +0000

Before starting the tutorial, it’s important to understand degrees and radians. In Excel, they are used in trigonometric functions such as SIN, COS, and TAN, which need the angle to be in radians for accurate calculations.

What is meant by Degrees?

A degree is a unit for measuring angles, where a full circle is divided into 360 equal parts. For example, A right angle is 90 degrees, which is one-quarter of a full circle (360 degrees).

What is meant by Radians?

A radian is the angle formed when the arc length equals the radius of a circle, with a full circle being 2π radians. If a circle has a radius of 5 cm and the arc length is also 5 cm, then the angle formed at the center is 1 radian.This is because an angle of 1 radian is defined when the arc length equals the radius.

Primary Differences Between Degrees & Radians:

Aspect	Degrees	Radians
Definition	Full circle = 360°	Full circle = 2π 2\pi radians
Conversion Formula	Multiply by π180 \frac{\pi}{180}	Multiply by 180π \frac{180}{\pi}
Use in Math	Common in geometry and daily use	Preferred in calculus and advanced math
Symbol	Uses “°”	No symbol (or sometimes “rad”)

Formula To Convert Degrees to Radians:

In Excel, the RADIANS function directly converts a given degree value into its equivalent radian values.

=RADIANS(B4) //outputs radian values.

Degrees	Radians
30	0.523598776
45	0.785398163
75	1.308996939
109	1.902408885
155	2.705260341
222	3.874630939
345	6.021385919
360	6.283185307

Converting Degree Values into Radians using RADIAN Function

By default, the RADIANS function in Excel returns the result with more than 10 decimal places. You can format the cell to limit the result to 2 decimal places, as shown in the example below.

To do this, You need to select the cells contains the output Radian values.
Right click on the cells and choose Format Cells.
Now, under the Category section, select Custom, and then choose the format “0.00” to display decimal values up to 2 decimal places.

Format cells to display decimal values up to 2 decimal places

The final formatted output is,

Formatted Output

Formula To Convert Radians to Degrees:

Similarly in Excel, the DEGREE function directly converts a given radian value into its equivalent degree values.

=DEGREES(B4) & "°" // ° is added to print the output with Degree symbol.

Radians	Degrees
0.35	20.0535228295788°
1.25	71.6197243913529°
2.1	120.321136977473°
3	171.887338539247°
4.15	237.777484979292°
5.4	309.397209370645°
6	343.774677078494°
7	401.070456591576°

Converting Radian Values into Degrees using DEGREE Function

As shown in the image above, the DEGREES function converts radian values into degrees. However, the result may display many decimal places, sometimes up to 10 digits. By using the ROUND Function or ROUNDDOWN Function, you can simplify the output to display the Degree values alone.

=ROUNDDOWN(DEGREES(A1), 0) & "°"  

or

=ROUND(DEGREES(A1), 0) & "°"

Rounded Degree Values using ROUND and ROUNDDOWN Function

Functions Used	Description
RADIAN	Convert angle from degrees to radians
DEGREE	Convert angle from radians to degrees
ROUND	Round a number to a specified number of digits
ROUNDDOWN	Round a number down towards zero

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How to Merge & Combine Columns without Losing Data in Excel?

Vigneshwaran Vijayakumar — Wed, 21 May 2025 03:07:00 +0000

Merging cells only keeps the upper-left and discards other values issue in Excel.

The issue “Merging cells only keeps the upper-left value and discards others” happens when you try to merge cells that contain more than one value. In this tutorial, I’ve explained all the methods to combine data from multiple columns without losing any information from your sheet.

Method 1: Using Clipboard to Merge Columns into a One Cell

The Clipboard method allows users to combine multiple columns into one cell easily. To use this method, you’ll need to open the Clipboard launcher in Excel.

Under the HOME tab in the left top of the sheet.
You may notice the word “Clipboard” on the screen. Next to it, there is a small arrow icon, which is the Clipboard Launcher. Click on it to open the clipboard panel.

Clipboard Launcher in Excel

Once the Clipboard launcher is open, select the columns you want to copy and press CTRL + C on your keyboard.
Now, the values from the selected columns are copied into the Clipboard launcher area, as shown in the image below.

Values are copied into the clipboard launcher area.

Now, select the target cell. In this case, let’s choose cell F10. Double-click on the cell to activate it, then press CTRL + V on your keyboard to paste the content into the cell.

Pasting the cell values into the cells without losing it’s contents

All values will now be added into the cell as one continuous block of text. However, the cell’s formatting and the formatting of its contents will be completely removed.

Before

After

Method 2: Merge Using Excel Formulas:

If you want to merge columns in Excel without losing any content, you can use basic Excel features like the ampersand (&) operator and other built-in Excel functions to do this easily.

Basic Merging Formula:

The basic formula to merge multiple columns without losing its data is,

=B4 & " " & C4 & " " & TEXT(D4, "dd-mm-yyyy")

By using the above formula, the contents from different columns will be combined with a space between them. The TEXT function is used to format the date properly so that it appears in the correct Excel date format.

Basic Excel Formula to Merge Column without Losing its values.

Merging With Space and Comma between the Values:

In the above example, the formula adds only a space between the contents. But in the example below, the formula adds a comma and a space while combining values from multiple columns. You can replace the comma with any other character or text based on your requirement.

=B4 & ", " & C4 & ", " & TEXT(D4, "dd-mm-yyyy")

Excel Formula to Merge Columns separated by comma and spaces between the values.

Method 3: Merge Multiple Columns Contents using Excel Functions

You can merge columns and their values in Excel using functions like TEXTJOIN or CONCAT. If you are using an older version of Excel, you can use the CONCATENATE function to combine the contents instead.

Function	It’s Purposes
TEXTJOIN	Combines text from multiple cells using a delimiter, optionally ignoring blanks.
CONCAT	Joins text from multiple cells or ranges into one string (modern version).
CONCATENATE	Joins text from multiple cells into one string (older version of CONCAT).

The Formulas are,

=TEXTJOIN(", ", TRUE, B4, C4, TEXT(D4, "dd-mm-yyyy"))

=CONCAT(B4, ", ", C4, ", ", TEXT(D4, "dd-mm-yyyy"))

=CONCATENATE(B4, ", ", C4, ", ", TEXT(D4, "dd-mm-yyyy")) //For older versions

Merged Columns using Excel Functions

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

Excel File Options Shortcuts List & It’s Explanations!

Vigneshwaran Vijayakumar — Mon, 19 May 2025 15:12:00 +0000

Here is a list of File Options shortcuts available in the Excel application. These shortcuts can help you use Excel more quickly and efficiently.

Create a New Workbook

To do	Windows	Mac
How to Create a new workbook	ctrl n	⌘ N

Using the Shortcut CTRL + N to create a New Workbook in Excel

As shown in the image above, pressing the CTRL + N keyboard shortcut in Excel creates a new workbook. By default, a blank workbook will be opened instantly when you use this shortcut.

Open a Workbook:

To do	Windows	Mac
How to open a workbook	ctrl o	⌘ o

Using the Shortcut CTRL + O to create Open a Workbook in Excel

As shown in the image above, pressing the CTRL + O keyboard shortcut in Excel opens a workbook. You can choose the Excel file from the list of saved files.

Save a Workbook:

To do	Windows	Mac
How to save a workbook	ctrl s	⌘ S

Using the Shortcut CTRL + S to create Save a Workbook in Excel

As shown in the image above, pressing the CTRL + S keyboard shortcut in Excel saves a workbook.

Save as Workbook:

To do	Windows	Mac
Save as Workbook	F12	⌘ ⇧S

Using the Shortcut F12 to create Save as a Workbook in Excel

As shown in the image above, pressing the F12 keyboard shortcut in Excel save as a workbook. You can choose the location and output filename of the Excel file.

Print a File:

To do	Windows	Mac
How to print a file in Excel	ctrl p	⌘ P

Using the Shortcut CTRL + P to create Print a Workbook in Excel

As shown in the image above, pressing the CTRL + P keyboard shortcut in Excel Print a workbook. Once you done choosing the printing options, you can print the Excel documents easily.

Open a Print Preview Window:

To do	Windows	Mac
How to open a print preview window in Excel	ctrl F12	Not Available

Using the Shortcut CTRL + F12 to create Print a Workbook in Excel

As shown in the image above, pressing the CTRL + F12 keyboard shortcut in Excel Open a Print Preview Window a workbook.

Close the Current Workbook:

To do	Windows	Mac
How to Close the current workbook	ctrl F4	⌘W

Using the Shortcut CTRL + F4 to close a Workbook in Excel

As shown in the image above, pressing the CTRL + F4 keyboard shortcut in Excel to close the current workbook.

Close Excel:

To do	Windows	Mac
How to Close Excel	ALT F4	⌘ Q

Using the Shortcut ALT + F4 to close Excel application

As shown in the image above, pressing the ALT + F4 keyboard shortcut in Excel to close the entire Excel Application.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How to Use Excel YEAR Function?

Vigneshwaran Vijayakumar — Tue, 13 May 2025 02:59:00 +0000

YEAR Function: A Brief

The YEAR function in Excel is a simple yet powerful tool used to extract the year from a given input date. This function is extremely useful when you are working with dates and need to separate or analyze the year part individually.

Objective	Value Returned by function
Aim to returns the year value	YEAR function in excel will return year value in 4 digits from the given input date.

The YEAR function was introduced in Excel version 1.0, which was released in 1985 for Macintosh and 1987 for Windows.

YEAR Function: Basic Syntax

=YEAR(serial_number) // Serial_number or Excel Date Value

serial_number: It refers to a valid Excel date from which you want to extract the year. If the serial_number value is a invalid date or a text value and then YEAR function will return the #VALUE! error.

The list of supported input formats that Excels YEAR function can handle as serial numbers:

Date entered directly, =YEAR("12/31/2023")
Cell reference containing a date, =YEAR(A1), where A1 = 01-Jan-2020
Excel serial number, =YEAR(44561) → returns 2022
DATE function, =YEAR(DATE(2022,5,10))
DATEVALUE function, =YEAR(DATEVALUE("July 1, 2021"))
NOW() or TODAY(), =YEAR(TODAY())
Text date strings that Excel can recognize, =YEAR("2022-03-15")

Basic Example of Using YEAR Function:

Basic Example of Using Excel YEAR Function

Input Date	Output Year	Comments
26-Feb-95	1995	YEAR function correctly extracts the year.
29-Feb-16	2016	YEAR returns the leap year correctly.
12345	1933	Excel interprets number as a serial date (Jan 18, 1933).
1900	1905	Excel treats the number as Feb 28, 1905 and returns 1905.
0	1900	Excel interprets 0 as Jan 0, 1900 and so returns the 1900.
13/32/2022	#VALUE!	Input date is invalid and so YEAR function returns error!
Not a date	#VALUE!	Text not recognized as a date and so YEAR function returns an error.
=YEAR(“”)	#VALUE!	Empty string causes YEAR to return an error.


Formula Used	=YEAR(serial_number)

Example Explanation:

In the first example, the formula extracts the year from 26-Feb-1995 and returns 1995, showing standard usage with a valid date.
In the second example, the formula is used with a leap day (29-Feb-2016). The YEAR function correctly identifies the year as 2016, proving it handles leap years well.
In the third example, the input is a number (12345), which Excel interprets as a serial date (Jan 18, 1933). The YEAR function then returns 1933.
In the fourth example, the number 1900 is treated as a serial date. Excel interprets it as Feb 28, 1905, and the YEAR function returns 1905.
In the fifth example, the input is 0, which Excel interprets as Jan 0, 1900. The YEAR function returns 1900 for this input.
In the sixth example, the date 13/32/2022 is invalid. Since there is no 32nd day in any month, the YEAR function returns an error: #VALUE!.
In the seventh example, the input is “Not a date”, a text string that Excel cannot recognize as a valid date. The YEAR function returns #VALUE!.
In the eighth example, the formula is =YEAR(“”). An empty string is not a valid input for date evaluation, so the function again returns #VALUE!.

Example of Using YEAR Function with Other Nested Functions:

Examples of using YEAR Function with other Nested Functions


Input Date	29 August 1995

S.no	Scenarios	Output	Formula Used	Comments
1	Extract the year from the date	1995	=YEAR(B4)	Basic use of YEAR function to extract year from B4
2	Check if the year is before 2000	TRUE	=YEAR(B4)<2000	Returns TRUE since 1995 is before 2000
3	Get age based on current year	30	=YEAR(TODAY()) – YEAR(B4)	Calculates age as of this year
4	Combine year with month name	1995-August	=YEAR(B4) & “-” & TEXT(B4,”mmmm”)	Merges year and full month name
5	Get first day of the year	01-Jan-95	=DATE(YEAR(B4),1,1)	Returns the first day of the same year
6	Get last day of the year	31-Dec-95	=DATE(YEAR(B4),12,31)	Returns the last day of the same year
7	Check if date is in leap year	FALSE	=IF(MOD(YEAR(B4),4)=0,IF(MOD(YEAR(B4),100)=0,MOD(YEAR(B4),400)=0,TRUE),FALSE)	Returns FALSE – 1995 is not a leap year
8	Convert to “Year Only” text format	1995	=TEXT(B4,”yyyy”)	Returns year as a text string, useful for formatting
9	Determine the decade	1990	=INT(YEAR(B4)/10)*10	Calculates the decade by rounding down to the nearest 10
10	Create a “Year-Quarter” label	1995-Q3	=YEAR(B4) & “-Q” & ROUNDUP(MONTH(B4)/3,0)	Builds a label combining year and calendar quarter

Example Explanations:

In the first example, the YEAR function is used with the date 29 August 1995 (in cell B4) and simply extracts the year. It returns 1995, which is the base usage of the function.
In the second example, the formula checks whether the extracted year is before 2000. Since 1995 < 2000, it returns TRUE.
In the third example, the formula subtracts the year in B4 from the current year using YEAR(TODAY()). If today is in 2025, it returns 30, representing the age.
In the fourth example, the formula combines the year with the full month name using the TEXT function. The result is “1995-August”, a dynamic text label.
In the fifth example, the formula constructs the first day of the year by setting the month and day to January 1. It returns 01-Jan-95, showing how to build the year’s start date.
In the sixth example, the last day of the year is constructed similarly, using December 31. The output is 31-Dec-95, the final date of that year.
In the seventh example, the formula checks whether the year in B4 is a leap year. Since 1995 is not divisible by 4, the formula returns FALSE.
In the eighth example, the formula formats the year as plain text using TEXT(B4,”yyyy”), returning “1995”. This is useful for exporting or merging with other text fields.
In the ninth example, the formula calculates the decade by removing the last digit and appending a 0, resulting in 1990, the decade to which the year belongs.
In the tenth example, the formula creates a “Year-Quarter” label by dividing the month number by 3 and rounding up. Since August is in Q3, the formula returns “1995-Q3”.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

How to Use Excel WEEKNUM Function?

Vigneshwaran Vijayakumar — Sat, 10 May 2025 16:02:00 +0000

WEEKNUM Function: A Brief

The WEEKNUM function in Excel is used to return the week number from a given input date. If the input is not a valid Excel date, the function will return a #VALUE! error. It will also return a #NUM! error if the return_type argument is not supported. The return_type must be 1, 2, or between 11 and 17.

Objective	Value Returned by function
Aim to returns the week number	WEEKNUM function in excel will return the relative week number to the given input date.

WEEKNUM Function: A Syntax

=WEEKNUM(serial_number, [return_type])

serial_number: The date you want to find the week number for.
return_type: A number that determines which day the week starts on. This is an optional one. By default the Excel will assumes Sunday as value 1.

The following is the difference between the WEEKNUM and ISOWEEKNUM function is,

WEEKNUM Function	ISOWEEKNUM Function
Can start on Sunday (default) or Monday (based on return type)	Always starts on Monday
Week 1 is the week containing January 1st	Week 1 is the first week with at least 4 days (ISO 8601 rule)
Varies depending on chosen return type (1 or 2)	Follows international ISO 8601 standard consistently

Basic Example of Using WEEKNUM Function:

Basic Example of Using WEEKNUM Function

The example covers the variable scenarios of using WEEKNUM function in practical days.

Input Date	Relative Day	Output Result
15-Jan-25	Wednesday	3
12-Feb-25	Wednesday	7
15-Mar-25	Saturday	11
10-Apr-25	Thursday	15
25-May-25	Sunday	22
15-13-2025	15-13-2025	#VALUE!
25-Dec-25	Thursday	52
01-Jan-26	Thursday	1

Formula Used for	Formula
Finding the Relative Day	=TEXT(B4,”dddd”)
Finding the WEEK Number	=WEEKNUM(B4)

In the above example, the first five input dates are valid Excel dates, so the WEEKNUM function returns the correct results.

However, the date 15-13-2025 is not a valid date, so the WEEKNUM function returns a #VALUE! error.

For the next input, the formula used is =WEEKNUM(B10, 99). Since 99 is not a valid return_type number, the function is supposed to return a #NUM! error. Otherwise, Excel may ignore the invalid return_type and treat it as 1, and then return a normal result.

A year has about 52 or 53 weeks depending on leap years. The below table contains the WEEKNUM value to the corresponding week range.

WEEKNUM Value	Approximate Dates (Range)
1	Jan 1 – Jan 7
2	Jan 8 – Jan 14
3	Jan 15 – Jan 21
4	Jan 22 – Jan 28
5	Jan 29 – Feb 4
6	Feb 5 – Feb 11
7	Feb 12 – Feb 18
8	Feb 19 – Feb 25
9	Feb 26 – Mar 3
10	Mar 4 – Mar 10
11	Mar 11 – Mar 17
12	Mar 18 – Mar 24
13	Mar 25 – Mar 31
14	Apr 1 – Apr 7
15	Apr 8 – Apr 14
16	Apr 15 – Apr 21
17	Apr 22 – Apr 28
18	Apr 29 – May 5
19	May 6 – May 12
20	May 13 – May 19
21	May 20 – May 26
22	May 27 – Jun 2
23	Jun 3 – Jun 9
24	Jun 10 – Jun 16
25	Jun 17 – Jun 23
26	Jun 24 – Jun 30
27	Jul 1 – Jul 7
28	Jul 8 – Jul 14
29	Jul 15 – Jul 21
30	Jul 22 – Jul 28
31	Jul 29 – Aug 4
32	Aug 5 – Aug 11
33	Aug 12 – Aug 18
34	Aug 19 – Aug 25
35	Aug 26 – Sep 1
36	Sep 2 – Sep 8
37	Sep 9 – Sep 15
38	Sep 16 – Sep 22
39	Sep 23 – Sep 29
40	Sep 30 – Oct 6
41	Oct 7 – Oct 13
42	Oct 14 – Oct 20
43	Oct 21 – Oct 27
44	Oct 28 – Nov 3
45	Nov 4 – Nov 10
46	Nov 11 – Nov 17
47	Nov 18 – Nov 24
48	Nov 25 – Dec 1
49	Dec 2 – Dec 8
50	Dec 9 – Dec 15
51	Dec 16 – Dec 22
52	Dec 23 – Dec 29
53 (if any)	Dec 30 – Dec 31 (only sometimes)

If the year is a leap year and then,

WEEKNUM	Approximate Dates
8	Feb 18 – Feb 24
9	Feb 25 – Mar 2

This WEEKNUM function returns different return types for WEEKNUM(), and each one decides which day the week starts. Refer to the table below:

Return Type	Week Starts On	Comments
1	Sunday	Default system, weeks start on Sunday
2	Monday	European system, weeks start on Monday
11	Monday	ISO standard variant, weeks start on Monday
12	Tuesday	Weeks start on Tuesday
13	Wednesday	Weeks start on Wednesday
14	Thursday	Weeks start on Thursday
15	Friday	Weeks start on Friday
16	Saturday	Weeks start on Saturday
17	Sunday	ISO standard where week 1 is the first week with at least 4 days

Using WEEKNUM Function With Other Nested Functions:

Using WEEKNUM Function with Other Nested Functions


Input Date:	10-Apr-25

Scenarios	Formula Used	Output	Comments
Simple WEEKNUM Usage	=WEEKNUM(B4)	15	Week number based on default (week starts Sunday)
WEEKNUM with Monday Start	=WEEKNUM(B4,2)	15	Week number assuming week starts on Monday
Check if Week is Even or Odd	=IF(MOD(WEEKNUM(B4),2)=0,”Even”,”Odd”)	Odd	Shows if the week number is even or odd
Using WEEKNUM Function with TODAY Function	=WEEKNUM(TODAY())	18	Shows current week’s number based on today’s date
Calculate Weeks Left in Year	=52-WEEKNUM(B4)	37	How many weeks are left in the year 2025
Highlight if given date lies in Q2	=IF(AND(WEEKNUM(B4)>=14,WEEKNUM(B4)<=26),”Q2″,”Not Q2″)	Q2	Checks if date falls into Quarter 2
Add 4 Weeks to Current Date	=B4+WEEKNUM(B4)*7/4	06 May 2025	Adds approximately 1/4th of the week span
Find Month from WEEKNUM	=MONTH(B4)	4	Displays month number, April is month 4
Custom Text for Early or Late Year	=IF(WEEKNUM(B4)<26,”First Half”,”Second Half”)	First Half	Classify date as First or Second half of the year
Combine Week Number and Year	=YEAR(B4)&”-W”&TEXT(WEEKNUM(B4),”00″)	2025-W15	Year-Week number format

Example Explanation:

In the first example, the WEEKNUM function is used with the date April 10, 2025 (10-Apr-2025) in cell B4. It calculates the week number assuming Sunday as the first day of the week and returns 15.
In the second example, the formula calculates the week number assuming Monday as the first day of the week. It also returns 15 for the same date.
In the third example, the formula checks if the week number is even or odd. Since 15 is an odd number, it returns “Odd”.
In the fourth example, the formula dynamically calculates the current week’s number based on today’s date. The result will automatically update each day.
In the fifth example, the formula subtracts the current week number from 52 to find out how many weeks are left in the year. For April 10, 2025, it returns 37 weeks remaining.
In the sixth example, the formula checks whether the week falls in Quarter 2. Since week 15 falls between week 14 and 26, it returns “Q2”.
In the seventh example, the formula adds about one-fourth of a week’s span to the current date, resulting in May 6, 2025 (06-May-2025).
In the eighth example, the formula extracts the month number from the date. Since April is the fourth month, it returns 4.
In the ninth example, the formula classifies the date into either the First Half or Second Half of the year based on its week number. April 10, 2025 falls in the First Half.
In the tenth example, the formula combines the year and the week number into a custom format like 2025-W15.

Functions Used in the Examples:

Function	Used for
WEEKNUM	Returns the week number of a date
IF	Performs logical checks to return different values based on a condition
MOD	Returns the remainder after a number is divided by a divisor (used to find even/odd)
TODAY	Returns the current date without time
AND	Checks if multiple conditions are TRUE
TEXT	Formats numbers/dates as text in a specified format
YEAR	Extracts the year from a date value
MONTH	Extracts the month number from a date value

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

{230+} Excel SHORTCUTS List for Windows & MAC

Vigneshwaran Vijayakumar — Thu, 08 May 2025 07:58:00 +0000

In this tutorial, you’ll find a complete and updated list of Excel shortcuts to help you work more efficiently. From basic navigation to advanced data operations, this tutorial covers everything you need. It includes shortcuts for both Windows and Mac users. You can bookmark the page for quick access or download the PDF version of the shortcuts list.

File Options Shortcuts:

Options	Windows	Mac
How to Create a new workbook	ctrl n	⌘ N
How to open a workbook	ctrl o	⌘ o
How to save a workbook	ctrl s	⌘ S
Save as Workbook	F12	⌘ ⇧S
How to print a file in Excel	ctrl p	⌘ P
How to open a print preview window in Excel	ctrl F12	Not Available
How to Close the current workbook	ctrl F4	⌘W
How to Close Excel	ALT F4	⌘ Q

Ribbon Options Shortcuts:

Options	Windows	Mac
How to Expand or collapse the ribbon	ctrl F1	⌘ ⌥ R
How to Activate access keys	ALT	Not Available
How to Move to next ribbon control	Tab	Tab
How to Activate or open selected control	Space	Space
How to Confirm control change	Enter	Return
How to Get help on the selected control	F1	Not Available

General Options Shortcuts:

Options	Windows	Mac
How to open options in Excel	ALT F T	⌘ ,
How to undo the last action in Excel	ctrl z	⌘ Z
How to redo the last action in Excel	ctrl y	⌘ Y
How to copy selected cells in Excel	ctrl c	⌘ C
How to repeat the last action in Excel	f4	⌘ Y
How to cut selected cells in Excel	ctrl X	⌘ X
How to paste content from clipboard in Excel	ctrl v	⌘ V
How to display the paste special dialog box in Excel	ctrl ALT V	⌘ ⌃ V
How to display find and replace in Excel	ctrl f	⌘ F
How to display find and replace, replace selected in Excel	ctrl h	⌃ H
How to Find the previous match in Excel	ctrl shift f4	⌘ ⇧ G
How to Find the next match in Excel	Shift f4	⌘ G
How to Create an embedded chart in Excel	Alt f1	Fn ⌥ F1
How to Create a chart in a new worksheet in Excel	f11	Fn F11

Table & Filters Options Shortcuts:

Options	Windows	Mac
How to insert a table in Excel	ctrl t	⌃ T
How to toggle auto filter in Excel	ctrl shift l	⌘ ⇧ F
How to activate filter in Excel	Alt ↓	⌥ ↓
How to select a table row in Excel	shift space	⇧ Space
How to select the table column in Excel	ctrl Space	⌃ Space
How to select a table in Excel	ctrl a	⌘ A
How to clear the slicer filter in Excel	alt c	⌥ C
How to toggle table total row in Excel	ctrl shift t	⌘ ⇧ T

Drag & Drop Options Shortcuts:

Options	Windows	Mac
How to Drag and cut in Excel	drag	Drag
How to Drag and copy in Excel	ctrl drag	⌥ Drag
How to Drag & Insert in Excel	shift drag	⇧ Drag
How to Drag and insert copy in Excel	ctrl shift drag	⌥ ⇧ Drag
How to Drag to a worksheet in Excel	ctrl drag	⌥ Drag
How to Drag to duplicate worksheet in Excel	alt drag	⌘ Drag

Navigation Options Shortcuts:

Options	Windows	Mac
How to Move one cell right in Excel	→	→
How to Move one cell left in Excel	←	←
How to Move one cell up in Excel	↑	↑
How to Move one cell down in Excel	↓	↓
How to Move one screen right in Excel	alt Pg.Dn	Fn ⌥↓
How to Move one screen left in Excel	alt Pg.up	Fn ⌥↑
How to Move one screen up in Excel	PG.Up	Fn ↑
How to Move one screen down in Excel	PG.Dn	Fn ↓
How to Move to the right edge of the data region in Excel	Ctrl →	⌃ →
How to Move to the left edge of the data region in Excel	Ctrl ←	⌃ ←
How to Move to the top edge of the data region in Excel	Ctrl↑	⌃ ↑
How to Move to the bottom edge of the data region in Excel	Ctrl ↓	⌃ ↓
How to Move to the beginning of a row in Excel	Home	Fn ←
How to Move to the last cell in a worksheet in Excel	ctrlend	Fn ⌃→
How to Move to the first cell in a worksheet in Excel	ctrlHome	Fn ⌃←
How to Turn End mode on in Excel	End	Fn →
How to Go back to hyperlink in Excel	ctrlGEnter	⌃ GReturn

Selection Options Shortcuts:

Options	Windows	Mac
How to Select an entire row in Excel	shiftspace	⇧Space
How to Select an entire column in Excel	ctrlspace	⌃Space
How to Select entire worksheet in Excel	ctrla	⌘A
How to Add adjacent cells to selection in Excel	ShiftClick	⇧Click
How to Add non-adjacent cells to selection in Excel	ctrlClick	⌘Click
How to Same selection in next column in Excel	Nil	⌥Tab
How to Same selection in the previous column in Excel	Nil	⌥⇧Tab
How to Move right between non-adjacent selections in Excel	ctrlalt→	⌃⌥→
How to Move left between non-adjacent selections in Excel	ctrlalt←	⌃⌥←
How to Toggle add to selection mode in Excel	shiftf8	Fn⇧F8
How to Cancel selection in Excel	Esc	Esc

Active Cell Options Shortcuts:

Options	Windows	Mac
How to Select an active cell only in Excel	shiftBackspace	⇧Delete
How to Show the active cell on a worksheet in Excel	ctrlBackspace	⌘Delete
How to Move active cell clockwise in selection in Excel	ctrl.	⌃.
How to Move active cell down in selection in Excel	Enter	Return
How to Move active cell up in selection in Excel	shiftenter	⇧Return
How to Move active cell right in a selection in Excel	tab	Tab
How to Move active cell left in a selection in Excel	shifttab	⇧Tab

Extend Selection Options Shortcuts:

Options	Windows	Mac
How to Extend selection by one cell right in Excel	shift→	⇧→
How to Extend selection by one cell left in Excel	shift←	⇧←
How to Extend selection by one cell up in Excel	shift↑	⇧↑
How to Extend selection by one cell down in Excel	shift↓	⇧↓
How to Extend the selection to the last cell right in Excel	ctrlshift→	⌃⇧→
How to Extend the selection to the last cell left in Excel	ctrlshift←	⌃⇧←
How to Extend the selection to the last cell up in Excel	ctrlshift↑	⌃⇧↑
How to Extend the selection to the last cell down in Excel	ctrlshift↓	⌃⇧↓
How to Extend selection up one screen in Excel	shiftpg.up	Fn⇧↑
How to Extend selection down one screen in Excel	shiftpg.dn	Fn⇧↓
How to Extend selection right one screen in Excel	altshiftpg.dn	Fn⇧⌥↓
How to Extend selection left one screen in Excel	altshiftpg.up	Fn⇧⌥↑
How to Extend selection to start of the row in Excel	shifthome	Fn⇧←
How to Extend selection to the first cell in a worksheet in Excel	ctrlshifthome	Fn⌃⇧←
How to Extend selection to last cell in the worksheet in Excel	ctrlshiftend	Fn⌃⇧→
How to Toggle extend selection mode in Excel	f8	FnF8

Select Special Options Shortcuts:

Options	Windows	Mac
How to Display ‘Go-To’ dialog box in Excel	ctrlg	⌃G
How to Select cells with comments in Excel	ctrlshifto	Fn⌃⇧O
How to Select the current region around the active cell in Excel	ctrlshift*	⇧⌃Space
How to Select the current region in Excel	ctrla	⌘A
How to Select the current array in Excel	ctrl/	⌃/
How to Select row differences in Excel	ctrl\	⌃\
How to Select column differences in Excel	ctrlshift\
How to Select direct precedents in Excel	ctrl[	⌃[
How to Select all precedents in Excel	ctrlshift{	⌃⇧{
How to Select direct dependents in Excel	ctrl]	⌃]
How to Select all dependents in Excel	ctrlshift}	⌃⇧}
How to Select visible cells only in Excel	alt;	⌘⇧Z

Cell Edit Mode Options Shortcuts:

Options	Windows	Mac
How to Edit the active cell in Excel	f2	⌃U
How to Insert or edit the comment in Excel	shiftf2	Fn⇧F2
How to Cancel entry in Excel	Esc	Esc
How to Select one character right in Excel	shift→	⇧→
How to Select one character left in Excel	shift←	⇧←
How to Move one word right in Excel	ctrl→	⌃→
How to Move one word left in Excel	ctrl←	⌃←
How to Select one word right in Excel	ctrlshift→	⌃⇧→
How to Select one word left in Excel	ctrlshift←	⌃⇧←
How to Select to the beginning of a cell in Excel	shiftHome	Fn⇧←
How to Select to end of the cell in Excel	shiftend	Fn⇧→
How to Delete to end of a line in Excel	ctrldelete	⌃Delete
How to Delete the character to the left of the cursor in Excel	backspace	Delete
How to Delete the character to the right of the cursor in Excel	delete	FnDelete
How to Start a new line in the same cell in Excel	altenter	⌃⌥Return

Entering data Options Shortcuts:

Options	Windows	Mac
How to Enter and move down in Excel	Enter	Return
How to Enter and move right in Excel	tab	Tab
How to Enter and move left in Excel	shifttab	⇧Tab
How to Complete entry and stay in the same cell in Excel	ctrlEnter	⌃Return
How to Enter the same data in multiple cells in Excel	ctrlEnter	⌃Return
How to Insert a current date in Excel	ctrl;	⌃;
How to Insert current time in Excel	ctrlshift:	⌘:
How to Fill down from cell above in Excel	ctrlD	⌃D
How to Fill right from cell left in Excel	ctrlr	⌃R
How to Copy a formula from the cell above in Excel	ctrl‘	⌃‘
How to Copy value from the cell above in Excel	ctrlshift“	⌃⇧“
How to Add hyperlink in Excel	ctrlk	⌘K
How to Display AutoComplete list in Excel	alt↓	⌥↓
How to Flash fill in Excel	ctrle	Not Available

Formatting Options Shortcuts:

Options	Windows	Mac
How to Format anything in Excel	ctrl1	⌘1
How to Display Format Cells with Font tab selected in Excel	ctrlshiftF	⌃⇧F
How to Apply or remove bold formatting in Excel	ctrlb	⌘B
How to Apply or remove italic formatting in Excel	ctrlI	⌘I
How to Apply or remove underscoring in Excel	ctrlu	⌘U
How to Apply or remove strikethrough formatting in Excel	ctrl5	⌘⇧X
How to Add or remove the shadow font style in Excel	Nil	⌘⇧W
How to Add or remove the outline font style in Excel	Nil	⌘⇧D
How to Align center in Excel	althac	⌘E
How to Align left in Excel	althal	⌘L
How to Align right in Excel	althar	⌘R
How to Indent in Excel	alth6	⌃⌥Tab
How to Remove indent in Excel	alth5	⌃⌥⇧Tab
How to Increase font size one step in Excel	althFG	⌘⇧>
How to Decrease font size one step in Excel	althFk	⌘⇧<

Number Formatting Options Shortcuts:

Options	Windows	Mac
How to Apply general format in Excel	CTRLSHIFT~	⌃⇧~
How to Apply currency format in Excel	CTRLSHIFT$	⌃⇧$
How to Apply percentage format in Excel	CTRLSHIFT%	⌃⇧%
How to Apply scientific format in Excel	CTRLSHIFT^	⌃⇧^
How to Apply date format in Excel	CTRLSHIFT#	⌃⇧#
How to Apply time format in Excel	CTRLSHIFT@	⌃⇧@
How to Apply number format in Excel	CTRLSHIFT!	⌃⇧!
How to Show the active cell on a worksheet in Excel	CTRLBACKSPACE	⌘DELETE
How to Move active cell clockwise in selection in Excel	CTRL.	⌃.
How to Move active cell down in selection in Excel	ENTER	RETURN
How to Move active cell up in selection in Excel	SHIFTENTER	⇧RETURN
How to Move active cell right in a selection in Excel	TAB	TAB
How to Move active cell left in a selection in Excel	SHIFTTAB	⇧TAB

Border Options Shortcuts:

Options	Windows	Mac
How to Add border outline in Excel	ctrlshift&	⌘⌥0
How to Add or remove border right in Excel	altr	⌘⌥→
How to Add or remove border-left in Excel	altl	⌘⌥←
How to Add or remove border-top in Excel	altt	⌘⌥↑
How to Add or remove border-bottom in Excel	altb	⌘⌥↓
How to Add or remove border upward diagonal in Excel	altd	Not Available
How to Add or remove border horizontal interior in Excel	alth	Not Available
How to Add or remove border vertical interior in Excel	altv	Not Available
How to Remove borders in Excel	ctrlshift_	⌘⌥_

Formula Options Shortcuts:

Options	Windows	Mac
How to Toggle absolute and relative references in Excel	f4	⌘ T
How to Open the Insert Function Dialog Box in Excel	shift f3	Fn ⇧F3
How to Autosum selected cells in Excel	alt =	⌘ ⇧T
How to Toggle formulas on and off in Excel	ctrl `	⌃ `
How to Insert function arguments in Excel	ctrl shift a	⌃ ⇧A
How to Enter array formula in Excel	ctrl shift Enter	⌃ ⇧Return
How to Calculate worksheets in Excel	f9	Fn F9
How to Calculate active worksheet in Excel	shift f9	Fn ⇧F9
How to Force calculate all worksheets in Excel	ctrl alt f9	Not Available
How to Evaluate part of a formula in Excel	f9	Fn F9
How to Expand or collapse the formula bar in Excel	ctrl shift u	⌃ ⇧U
How to Display function arguments dialog box in Excel	ctrl a	⌃ A
How to Define a name in Excel	ctrl f3	Fn ⌃F3
How to Define name using row and column labels in Excel	ctrl shift f3	Fn⌃⇧F3
How to Paste name into a formula in Excel	f3	Not Available
How to Accept function with autocomplete in Excel	tab	↓ Tab

Grid Options Shortcuts:

Options	Windows	Mac
How to Display Insert Dialog box in Excel	ctrlshift+	⌘ ⇧+
How to Insert rows in Excel	ctrlshift+	⌘ ⇧+
How to Insert columns in Excel	ctrlshift+	⌘ ⇧+
How to Display Delete dialog box in Excel	ctrl –	⌘ –
How to Delete rows in Excel	ctrl –	⌘ –
How to Delete columns in Excel	ctrl –	⌘ –
How to Delete cells in Excel	ctrl –	⌘ –
How to Delete the contents of selected cells in Excel	Delete	Fn Delete
How to Hide columns in Excel	ctrl 0	⌃ 0
How to Hide rows in Excel	ctrl 9	⌃ 9
How to Unhide rows in Excel	ctrl shift 9	⌃ ⇧9
How to Unhide Columns in Excel	ctrl shift 0	⌃ ⇧0
How to Group rows or columns in Excel	alt shift →	⌘ ⇧K
How to Ungroup rows or columns in Excel	alt shift ←	⌘ ⇧J
How to Open Group Dialog Box in Excel	alt shift →	⌘ ⇧K
How to Open Ungroup Dialog Box in Excel	alt shift ←	⌘ ⇧J
How to Hide or Show outline symbols in Excel	ctrl 8	⌃ 8
How to Zoom in in Excel	ctrl alt +	⌥ ⌘+
How to Zoom out in Excel	ctrl alt –	⌥ ⌘–

Pivot Tables Options Shortcuts:

Options	Windows	Mac
How to Select the entire pivot table in Excel	ctrl a	⌘ A
How to Toggle pivot table field checkbox in Excel	space	Space
How to Group pivot table items in Excel	alt shift →	⌘ ⇧K
How to Ungroup pivot table items in Excel	alt shift ←	⌘ ⇧J
How to Hide pivot table item in Excel	ctrl –	Ctrl –
How to Create a pivot chart on the same worksheet in Excel	alt f1	Not Available
How to Create a pivot chart on a new worksheet in Excel	f11	Fn F11
How to Create a pivot chart on a new worksheet in Excel	alt d p	⌘ ⌥P

Workbook Options Shortcuts:


How to Insert new worksheet in Excel	Shiftf11	Fn ⇧F11
How to Go to next worksheet in Excel	ctrl pg.dn	Fn ⌃↓
How to Go to the previous worksheet in Excel	ctrl pg.up	Fn ⌃↑
How to Move to next pane in Excel	shiftf6	Fn ⇧F6
How to Go to next workbook in Excel	ctrl tab	⌃ Tab
How to Go to the previous workbook in Excel	ctrl shift tab	⌃ ⇧Tab
How to Minimize the current workbook window in Excel	ctrl f9	⌘M
How to Maximize the current workbook window in Excel	ctrl f10	Fn ⌃F10
How to Select adjacent worksheets in Excel	shift Click	⇧ Click
How to Select non-adjacent worksheets in Excel	ctrl Click	⌘ Click
How to Toggle scroll lock in Excel	Scrlk	Fn ⇧F14
How to Toggle full screen in Excel	ctrl shift f1	⌃ ⌘F

Dialog Boxes Options Shortcuts:

Options	Windows	Mac
How to Move to the next control in Excel	tab	Tab
How to Move to the previous control in Excel	shift tab	⇧ Tab
How to Move to the next tab in Excel	ctrl tab	⇧ Tab
How to Move to the previous tab in Excel	ctrl shift tab	⌃ ⇧Tab
How to Accept and apply in Excel	Enter	Retrn
How to Check and uncheck the box in Excel	space	Space
How to Cancel and close the dialog box in Excel	Esc	Esc

Other Options Shortcuts:

Options	Windows	Mac
How to Open Spelling dialog box in Excel	f7	Fn F7
How to Open Thesaurus dialog box in Excel	shift f7	Fn ⇧F7
How to Open Macro dialog box in Excel	alt f8	Fn ⌥F8
How to Open VBA Editor in Excel	alt f11	Fn ⌥F11
How to Duplicate object in Excel	ctrl D	⌘ D
How to Snap to the grid in Excel	alt	⌘
How to Hide or show objects in Excel	ctrl 6	⌃ 6
How to Display Modify Cell Style dialog box in Excel	alt ‘	⌘ ⇧L
How to Display the right-click menu in Excel	Shift f10	Fn ⇧F10
How to Delete a comment in Excel	Shift f10D	Fn ShiftF10
How to Display control menu in Excel	alt space	?
How to Move to the previous tab in Excel	ctrl shift tab	⌃ ⇧Tab
How to Accept and apply in Excel	Enter	Return
How to Check and uncheck the box in Excel	space	Space
How to Cancel and close the dialog box in Excel	Esc	Esc

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.