You are reading the article How To Perform Spell Check In Excel (Examples) updated in November 2023 on the website Cancandonuts.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested December 2023 How To Perform Spell Check In Excel (Examples)
Spell Check in Excel (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Introduction to Spell Check in Excel Uses of Spell Check in ExcelSpell Check is used to identify spelling errors, typing errors, or any kind of duplicate words (I will explain it with an example) in Excel. It may be a useful tool when using some texts repetitively in Excel. Although many of us play with numbers in Excel, spell check is crucial in creating some reports and datasets that may have text. So, nobody will want to see any errors in it, or you don’t want to upset your managers/bosses with these mistakes.
Examples of Spell Check in Excel
In the below screenshot, I have the conversation between 2 guys saved in Excel. Don’t go with the content; it’s purely to make you understand better.
Screenshot 1
In Screenshot 1, you can see there are a lot of typing errors and spelling mistakes.
To run spell check in this particular workbook, you must activate the cells where you want to check spelling or typing errors and go to the “REVIEW” tab in the Excel ribbon, and there you will find the spell check option.
A shortcut to run spell-check is simply pressing the “F7” button from your keyboard after activating the cells/worksheet.
Screenshot 2
In the above Screenshot 2, you can notice that I have selected the A1 cell and the spell check option in the review tab.
Key Point: If you select cell A1, it will go through all the cells horizontally, i.e., all columns of the first row, then move to the second row and check all the column cells in the second row (from left to right) and then move to the third row and so on.
A spelling dialogue box will be displayed after going on to the last cell, where any value is stored and identifies any spelling error.
If you want to start to spell check from somewhere else, let’s take an example, cell D5, it will simply go through all cells of Row 5 and then rows below it, and when done will all rows after D5, it will ask you, “if you want to continue checking at the beginning of the sheet?”. Some dialogue boxes like the below screenshot will appear. If you select yes, it will check the remaining cells (A1 to D5 in our case) and shows suggestion. To make you understand better, I have selected the D5 cell and pressed F7 now; it will check all the cells after it, give spelling correction suggestions & after it will ask you to check the remaining cells or not and work accordingly. For your better understanding, please go through the below Screenshots.
Screenshot 3
Pressed F7 after activating B13 cell, now spell check dialogue box will appear with correction suggestions.
Screenshot 4
The results of the cell B13 correction through Spell Check is mentioned in the screenshot.
Screenshot 5
After completing the last cell with correction(C13), it has asked to check from the beginning of the sheet. As discussed above.
Features of Spell Check in Excel Dialog Box
You might have noticed in the screenshots that many spell-check options are available. Let us discuss them one by one.
Add to Dictionary: If there is an error in any text, but you anticipate using the word frequently in the future (such as a name or abbreviation), you can add it to the dictionary in Excel. It will become part of the dictionary and a list of acceptable words. Any word added to Excel’s Dictionary will not be detected as an error in the future in any workbook.
Change: you can use it when you want to change a word according to suggestions, and it will change it according to the selected suggested word. (You can see in Screenshot 3 the words suggested for text in cell C5.)
Change all: It will replace all the instances of that word in a worksheet according to the selected suggestion.
Autocorrect: If you go with this option, it will autocorrect the word with a selected suggestion, and it will be added to the autocorrect list viz; every time you type that word, it will be automatically corrected according to the selected suggestion.
Dictionary Language: We can change the dictionary used in Excel.
Options: Used to change the Excel default settings (discussed later in this guide), and it will open an option box where you can make changes accordingly.
Now I chose to continue to spell check from the beginning of the sheet. And chose options accordingly. All spell checks are done.
In the above Screenshot 6, you might have noticed some spelling errors in the worksheet, but Excel isn’t detecting them. Have you noticed? It has brought us to notice the default settings of the Spellcheck, and they are:
It ignores all errors in the upper case. For e.g., the words “AAM” and “FIINE” in cells C4 & D4 are not marked as errors.
All words with numbers in them will be ignored too. (cell B12 “newww123”)
It also ignores all internet addresses and file addresses. (D7 & B12)
Screenshot 7
Another important feature to note here is Spell check doesn’t ignore repeated words. You can identify it by comparing Screenshot 1 and Screenshot 6 carefully.
Cell F4 has “how are you?” and spell check has detected it. I have deleted it with the delete option, which only appears when we have any kind of repetition in Excel.
Things to Remember
Excel uses spell check to identify spelling errors.
It detects the repetition of words.
You can ignore, change, autocorrect, or add to the dictionary all detected errors according to your requirement.
It doesn’t detect errors in words written in uppercase or have numbers in it.
It ignores all file and internet addresses.
Recommended ArticlesThis is a guide to Spell Check in Excel. Here we discuss Spell Check in Excel and How to use Spell Check in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –
You're reading How To Perform Spell Check In Excel (Examples)
Address In Excel (Formula, Examples)
ADDRESS in Excel
Address Function is a pre-built integrated Excel function categorized under Lookup and Reference functions. Address Function in Excel is used to find out the address of a cell in a worksheet. ADDRESS Function in Excel returns or displays the address of a cell based on a given row and column number.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
ADDRESS Formula in Excel
Below is the ADDRESS Formula in Excel :
=ADDRESS (row, column, [ref_type], [ref_style], [sheet_name] )
The ADDRESS Function in Excel has the below-mentioned arguments:
Row or row_num: (Compulsory or required parameter) It is a row number of the cell address.
Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address.
[abs_num] or ref_type: (optional argument) It is a numeric value specified to obtain the reference type below.
Note: If this parameter or argument is omitted or not entered, it returns an Absolute reference of the cell, e.g., $A$1. This argument allows you to select the numeric value below (1 to 4) based on the output requirement.
Different types of referencesValue Explanation
1 or Default Absolute referencing, e.g., $A$1
2 Relative column & absolute row, e.g. A$1
3 Absolute column & relative row e.g. $A1
4 Relative referencing, e.g., A1
[ref_style] or [a1] : (optional argument) It is a logical value indicating whether to use standard (A1 style) cell reference format or R1C1 style cell reference format. This argument allows you to select the below-mentioned ref_style.
If this parameter or argument is omitted or not entered, it will consider the default value 1 or TRUE (A1 style).
Note: Excel has two types of cell reference styles, i.e., A1 & R1C1.
In the A1 reference style, the column name is mentioned as an alphabet, and the row name as a number, Whereas in the R1C1 reference style, both row and column are mentioned in numbers.
[sheet_text]) or [sheet_name]: (optional argument) It is the name of the worksheet to return. Or the worksheet name is mentioned in quotes, e.g., “Sales”.
Note: No sheet name appears if this argument is omitted or left blank.
How to Use Address Function in Excel?The ADDRESS Function in Excel is very simple and easy to use. Let us understand the working of the ADDRESS Function in Excel by some ADDRESS Formula in Excel example.
You can download this ADDRESS Function Excel Template here – ADDRESS Function Excel Template
Example #1In the below-mentioned example, I have a row number in Cell “B11” & a column number in cell “C11”. Here I need to find the cell address with row & column numbers.
Let’s apply the ADDRESS function in cell “D11”. Select the cell “D11,” where the ADDRESS function needs to be applied.
A dialog box appears where arguments for the ADDRESS function need to be filled or entered i.e.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
Column or Column_num: (Compulsory or required parameter) It is a Column number of the cell address. Here it is, 8.
[abs_num] or ref_type: (optional argument) This argument is omitted or not entered. By default, it will consider 1, i.e., Absolute reference.
[ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).
[sheet_text]) or [sheet_name]: (optional argument) This argument is omitted or not entered; it is left blank. By default, no sheet name appears.
=ADDRESS(B11,C11) or =ADDRESS(2,8)
When the above parameters or arguments, i.e. ([abs_num], [a1], [sheet_text]), are not mentioned, then the ADDRESS function results in or returns a pattern of absolute address with row and column name (i.e., $H$2).
$H indicates the Absolut column (8), whereas $2 indicates the Absolute Row (2).
Example #2In the below-mentioned example, I have a row number in Cell “B16,” & a column number in cell “C8,” & Reference type or abs_num as 3 in cell “D16”. Here I need to find out the cell address & it should reflect as Absolute Column & Relative Row in Cell Address.
Let’s apply the ADDRESS function in cell “E16”. Select the cell “E16”. where the ADDRESS function needs to be applied.
A dialog box appears where arguments for the ADDRESS function need to be filled or entered i.e.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address. Here it is, 8.
[abs_num] or ref_type: (optional argument) Here, in the cell address, output is to be returned as an absolute column & relative row in cell address. Therefore, we have to enter 3.
[ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).
[sheet_text]) or [sheet_name]: (optional argument) This argument is omitted or not entered. It is left blank. By default, no sheet name appears.
=ADDRESS(2,8,3)
ADDRESS function results in or returns a pattern of absolute column & relative row in cell address (i.e., $H2).
Here, $H indicates the Absolut column, whereas 2 indicates the relative Row (2).
Things to Remember#VALUE! error occurs if the row or column number argument is less than 1. If the Column number is greater than the number of columns in Excel (16,384 Columns). If the row number exceeds the number of rows in Excel (10,48,576 rows).
In the ADDRESS function argument, if row_num, column_num, or [abs_num] is a nonnumeric value, it returns #VALUE! Error.
Recommended ArticlesThis has been a guide to ADDRESS in Excel. Here we discuss the ADDRESS Formula in Excel and How to use the ADDRESS Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
Stdev Function In Excel (Formula, Examples)
STDEV Function in Excel (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
STDEV Function in ExcelStdev in Excel is used for calculating the standard deviation of any data set. Standard Deviation shows how one value deviates to another with how many digits. Stdev is used only for sample data, whereas we have other Standard Deviation functions such as STDEV.P for the entire population. To calculate the standard deviation of sample data, we have a new function which is STDEV.S. To calculate standard deviation, select the range of numbers.
STDEV Formula in ExcelBelow is the STDEV Formula in Excel :
Examples of STDEV function in Excel1) Let us take a look at some examples of STDEV. STDEV is widely used to measure how much the individual data elements are dispersed from the average value.
You can download this STDEV Function Excel Template here – STDEV Function Excel Template
For example, let’s say we have a dataset of parts for a store which is being checked for quality. We need to find the standard deviation between the number of parts we have, the number of parts to be reworked, parts that failed, the parts yet to be checked, and the total number of parts.
The function STDEV will use like this:
This would give us the standard deviation for values in the cells A2 till F2. Similarly, we can find out the standard deviation for others.
The above data analysis means that the difference between the number of parts for each category is high. If the formula returned a lower number, it would mean that the number of parts of each category is closer. This is an important factor in many applications, such as analyzing the effectiveness of quality control measures.
2) Let’s look at another example where the STDEV function is useful.
Suppose we have weight data for a number of shipments. We want to find out how much they differ from each other.
The data is as below:
Shipment 1: 0.5kg
Shipment 2: 2 kg
Shipment 3: 0.8 kg
Shipment 4: 2.8 kg
Shipment 5: 1.1 kg
We put this in an Excel sheet.
Now Use the STDEV function to get the standard deviation:
This would give us the standard deviation in cell B7.
Now we find that the standard deviation is about 0.9, which means that the weights do not vary much between the shipments. Such problems contain many more data points in real life and use more measures such as average and variance to create solutions to specific problems.
Explanation of the STDEV functionIn this formula, one of the values (say 1, etc.) is the mean or average of the numbers, while n is the total sample size. Standard deviation measures how widely each value in a sample varies from the average value. It is the square root of the variance. Variance is the squared differences’ average from a dataset’s mean or average.
How to Use the STDEV Function in ExcelNow that we have an overview of the statistics underlying the STDEV function in Excel, and a few examples of STDEV functions’ uses, let us look at how to use the function and the correct syntax.
First, we need one or more data points to choose from. Once we have done that, we need to pick a cell where we want the result displayed.
Now type the function and arguments correctly and press F2 and Enter.
And the result will be :
The syntax of the STDEV function is as follows:
In STDEV, the number 1 is called a mandatory argument and corresponds to a sample of the data. The number 2 and other numbers are optional arguments but should correspond to a dataset sample to get correct results.
STDEV has some related functions for use with different types of datasets; the most commonly used are the following:
STDEV.S for a numeric data sample does not calculate standard deviation based on text and logical values.
STDEVA for calculations based on logical values and text
We can also use the STDEV function from the formula bar, as shown below:
Pick a dataset and select any cell in which we would like to display the result.
Then select the data that needs analysis.
And the result will be :
Things to Remember About the STDEV Function in ExcelWe have seen a few examples of the STDEV function and have also seen how to use it in problems of data analysis in the examples; here are a few things to remember:
When using STDEV, the function assumes that the sample is the entire size of the population. So when we want to determine the standard deviation from a subset of the whole data, we should use STDEV.S
The standard deviation formula used in STDEV is called the (n-1) method.
The arguments in STDEV can be numbers or text, arrays of different types, and references containing numbers.
The calculation in STDEV uses logical values and text typed directly in the arguments. If the logical values or text are referenced from an array, empty cells, or error values in the reference, STDEV ignores them.
To use logical and text values referenced from elsewhere and not typed directly in the argument, we should use the STDEVA function.
Population in statistics means the entire dataset we are using. A sample is a part of the dataset which we pick to analyze.
Conclusion – STDEV Function in ExcelData science is one of the most important areas of study currently. Excel is a tool we can use in many data science applications without resorting to high-level programming or having an in-depth knowledge of the mathematics involved. STDEV and many other such functions make it much easier for us to be data analysts. However, we need to know the basics and think about creative solutions to apply them.
Recommended ArticlesThis has been a guide to STDEV Function in Excel. Here we discuss the STDEV Formula in Excel and How to use the STDEV function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
10 Text Functions In Excel With Examples
Excel is all about working with numbers. However, if your data consists of too much text, you don’t have to worry at all. Excel provides several functions that make it easier to manipulate text strings. These functions let you easily find a string, count the characters in a string, remove extra spaces from a string, join two or more strings, and perform other similar tasks on the textual data.
What are Text functions in Excel?Text Functions are Microsoft Excel’s native functions that allow transforming or analyzing textual data. Excel provides a total of 30+ Text functions and many of these are often used by people for data analysis. This post highlights 10 such Text functions, with their uses and examples.
10 Text Functions in Excel with ExamplesFollowing is the list of top 10 functions in Excel:
FIND
LEN
LEFT
RIGHT
MID
SUBSTITUTE
UPPER
TRIM
CONCATENATE
TEXT
Let’s take a detailed look at these functions, one by one.
1] FINDThe FIND function allows you to find a text string within another. It returns the position at which a character or string begins within another text string.
Syntax
FIND(find_text, within_text, [start_num])
find_text argument is used to enter the text the user wants to search.
within-text argument takes the text which contains the text that needs to be found.
[start_num] is an optional argument that takes the position from where to start the search. It takes the value 1 by default.
Example
Let us say the A3 cell in an Excel sheet contains the string ‘The Windows Club’. If the user wants to find the position of ‘Win’ within the string, he may use the ‘Find’ functions as:
f(x)=FIND("Win", A1)The output of the above function will be 5, as 5 represents the starting position of the text ‘Win’ within ‘The Windows Club’.
Note: The FIND function is case-sensitive. If you do not want to match the case, you can use the SEARCH function, which has the same syntax as the FIND function.
Read: How to use the new TEXTSPLIT function in Excel
2] LENThe LEN function calculates the length of the string, i.e. the number of characters present in a string. It counts the spaces as characters.
Syntax
LEN(text)
text argument takes the string whose length the user wants to find.
Example
In the above example, if the user wants to find the length of the string ‘The Windows Club’, he may use the ‘LEN’ function as:
f(x)=LEN (A3)The output of the above function will be 16, as there are 16 characters in the string ‘The Windows Club’, including spaces.
Also read: Arrows keys are not working in Microsoft Excel.
3] LEFTThe LEFT function returns several successive characters from the left side of a string, based on the number specified by the user.
Syntax
LEFT(text, [num_chars])
text argument is used to specify the string that contains the characters that need to be found.
[num_chars] specifies the number of characters to be extracted from the left of the main string. This argument is optional. It takes ‘1’ as a default value, if not specified by the user.
Example
In the same example as stated above, if the user wants to extract the first 7 characters from ‘The Windows Club’, he may use the ‘LEFT’ function as:
f(x)=LEFT (A3, 7)The output of the above function will be The Win, as these are the 7 leftmost characters in the string ‘The Windows Club’, including spaces.
4] RIGHTThe RIGHT function is used to extract several characters from the extreme right of a string.
Syntax
RIGHT(text, [num_chars])
text argument specifies the string that contains the desired characters.
[num_chars] argument specifies the number of characters that need to be extracted, moving from the extreme right to the left of the string. This is an optional argument that takes ‘1’ as the default value if left unspecified.
Example
Taking the same example, if the user wants to extract the last 7 characters from the string ‘The Windows Club’, he may use the ‘RIGHT’ function as:
f(x)=RIGHT(A3, 7)The output of the above function will be ws Club, since they are the 7 rightmost characters in ‘The Windows Club’, including spaces.
5] MIDThe MID function returns several consecutive characters or a substring from the middle of another string.
Syntax
MID(text, start_num, num_chars)
text argument takes the string that contains the desired characters.
start_num argument takes the position from where to start extracting the characters.
num_chars argument takes the number of characters the user wants to extract from the string.
Example
In the above example, if the user wants to extract 4 characters starting from the 3rd character in the string ‘The Windows Club’, he may use the ‘MID’ function as:
f(x)=MID(A3, 3, 4)The output of the above function will be e Wi, as ‘e’ is the third character and staring from ‘e’ counting spaces as well, ‘e Wi’ are the 4 consecutive characters in the string ‘The Windows Club’.
6] SUBSTITUTEThe Substitute function replaces an existing text with a new text in a given string.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])
text argument specifies the main string.
old_text argument specifies the text that needs to be replaced.
new_text argument specifies the text that needs to be put in place of the existing text.
[instance_num] argument specifies which instance (or occurrence) of the existing text is to be replaced. This is an optional argument. If you specify this value, only that instance of the text will be replaced; otherwise, all the instances of the existing text will be replaced with the new text.
Example
In the same example, if the user wants to substitute ‘Welcome to The’ for ‘The’ in ‘The Windows Club’, he may use the ‘SUBSTITUTE’ function as:
f(x)=SUBSTITUTE(A3, "The", "Welcome to The")The output of the above function will be Welcome to The Windows Club, as the substitute function has replaced ‘The’ with ‘Welcome to The’ in the text string ‘The Windows Club’.
7] UPPERThe UPPER function converts a string into uppercase, i.e., it returns a string after capitalizing each letter.
Syntax
UPPER(text)
text argument takes the string that needs to be capitalized.
Example
Following the same example, if the user wants to capitalize each letter in the string ‘The Windows Club’, he may use the ‘UPPER’ function as:
f(x)=UPPER(A3)The output of the above function will be THE WINDOWS CLUB.
Note:
If you want to convert a string into lowercase, you may use the LOWER function, having the same syntax as that of the UPPER function.
If you want to capitalize the first letter of each word in a string, you may use the PROPER function with the same syntax.
8] TRIMThe TRIM function removes all the extra spaces within a string, leaving just 1 space between two words.
Syntax
TRIM(text)
text argument takes the string with irregular spacing.
Example
In the example stated above, if the user wants to remove unnecessary spaces from the string ‘The Windows Club’, he may use the ‘TRIM’ function as:
f(x)=TRIM(A3)The output of the above function will be The Windows Club, leaving just a single space between words.
9] CONCATENATEThe CONCATENATE function joins two or more strings in Excel.
Syntax
CONCATENATE(text1, [text2], ...)
text1 argument is mandatory. It takes the first string to join.
text2 argument takes the additional string to join. You may join up to 255 strings.
Example
Let us say the A3 cell in an Excel sheet contains the string ‘The’, the A4 cell contains the string ‘Windows’, and the A5 cell contains the string ‘Club’. If the user wants to join these strings, he may use the ‘CONCATENATE’ functions as:
f(x)=CONCATENATE(A3, " ", A4, " ", A5)The output of the above function will be The Windows Club, joining the strings in A3, A4, and A5 cells with spaces between these strings.
Tip: Use the ampersand (&) symbol to concatenate two text strings.
10] TEXTThe TEXT function converts the format of a number from ‘numeric’ to ‘text’. The function can be used to place formatted numbers between text.
Syntax
TEXT(value, format_text)
value argument takes the numerical value that needs to be formatted.
format_text argument takes the format that needs to be applied to the value.
Example
Let us say the A2 cell in Excel contains the string ‘The Windows Club started on’ and the A3 cell contains the numeric data ’20-04-2009′; the two of these can be combined in a single sentence using the ‘CONCATENATE’ and the ‘TEXT’ functions as:
f(x)=A2&" "&TEXT(A3,"mmmm d, yyyy")&"."The output of the above functions will be The Windows Club started on April 20, 2009.
Also read: How to convert currencies in Excel.
What is an example of a text function?The TEXT function in Excel is used to join a formatted number with a text string. For example, if an Excel sheet contains the string ‘Retails sales surge by’ in cell A1, and the number ‘20000’ in cell A2, then TEXT function can be used to join the content of these two cells as:
f(x)=A1&" "&TEXT(A3,"$ ##,###")&".".The above function will return ‘Retails sales surge by $20,000.’, where the number 20000 has been formatted using a currency symbol and comma separator.
What is the use of lower function?The LOWER function is used to change the case of a string to lowercase. If a given string is in uppercase, proper case, or sentence case, the LOWER function will return the string with each of its alphabet converted in small letters. The syntax for LOWER function is LOWER(text), where text specifies the string or reference to the cell that contains the string that needs to be converted into lowercase.
Read Next: Top 15 Financial functions in Microsoft Excel.
How To Freeze Rows In Excel?
Freeze Rows in Excel
In Excel, we have a function for freezing the rows as well. With the help of Freeze Row, we can fix the pane or row we select to go beyond the certain limit worksheet. Freeze Row can be accessed from the View menu tab’stab’s Window section from the drop-down list of Freeze Panes. First, to freeze the column, select the column we want to freeze, put the cursor anywhere in that column, and then select the Freeze Column option from the list. We will be able to see the selected column is now frozen.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Here we have 3 options. Freeze Panes, Freeze Top Row, and Freeze Top Column. I will show you examples of Freeze Rows in this article.
How to Freeze Rows in Excel?We use Freeze Panes in Excel to keep an area or heading of a worksheet visible while scrolling to another part of a worksheet. This is very useful and convenient when working on a large table. So we select Freeze Panes from the View tab and keyboard shortcuts to lock that area.
You can download this Freeze Rows Excel Template here – Freeze Rows Excel Template
Let’s understand how to Freeze Panes in Excel with some examples.
Freeze or Lock Top Row – Example #1Usually, our header is located in each column, i.e., horizontally. When scrolling down, we need to lock or freeze our first or top row, enabling us to see the first or top row at any given time.
Now look at the below image of sales data in the sheet.
It isn’t easy to see all the headers when scrolling down. So, in Excel, we have an option called Freeze Top Row, which holds onto the top row when scrolling down and helps us see the heading all the time.
Let’s look at the below steps to understand the method.
Step 1: Select the worksheet where you want to freeze your top row.
Step 3: Ok, done. It is as easy as you eat pizza. You have frozen your top row to see the top row while scrolling down.
Even though I am in the 281st row, I can still see my headers.
Freeze or Lock Multiple Rows – Example #2We have seen how to freeze the top row in the Excel worksheet. I am sure you found it a walk-in-the-park process; you did not have to do anything special to freeze your top row. But we can freeze multiple rows as well. So here, you need to apply simple logic to freeze multiple rows in Excel.
Step 1: Identify how many rows you need to freeze in the Excel worksheet. For an example, take the same data from the above example.
Place a cursor on the A8 cell. This means I want to see all the rows above the 8th row.
Step 2: Remember we are not only freezing the top row, but we are freezing multiple rows at a time. Do not press ALT + W + F + R in a hurry; hold on momentarily.
After selecting cell A8 under freeze panes, again select the option Freeze Panes under that.
Now we can see a tiny grey straight line below the 7th row. This means the above rows are locked or frozen.
You can keep seeing all 7 rows while scrolling down.
Things to Remember
We can freeze the middle row of the Excel worksheet as your top row.
Make sure the filter is removed while freezing multiple rows at a time.
If you place a cursor in the unknown cell and freeze multiple rows, then you may go wrong in freezing. Make sure you have selected the right cell to freeze.
Recommended ArticlesThis has been a guide to Freeze Rows in Excel. Here we discussed How to Freeze Rows in Excel and different methods and shortcuts to Freeze Rows in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles to learn more –
How To Use Calendar In Excel?
Calendar in Excel
Excel Calendar is a customized calendar in which we can create our calendar with the help of a template available. The main problem comes when we must decide what the first day of the year will be. For the Year 2023, we all know that the first day started on Wednesday. To decide the day of 1st Jan, use the DATE function to find the first date of the month and then subtract the 1st Weekday Date using the WEEKDAY function along with DATE.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Excel provides a variety of different templates which is already formatted and ready to use.
How to use the pre-built calendar in Excel?To create a pre-built calendar in Excel, follow the below process:
You can download this Calendar Excel Template here – Calendar Excel Template
Go to the File tab.
It will open several options in the right side window. Refer to the below screenshot.
In this window, we have to enter what we are looking for in the search box.
Type Calendar in the box and press Enter key like the below screenshot.
It will display various types of calendar options, as shown below.
It has many options like monthly and yearly calendar formats.
Scroll the bar and choose the Photo calendar (Mon) option. Refer to the below screenshot.
It will open an Excel workbook having a month-wise date calendar.
Here month-wise tab is available, as shown in the screenshot below.
It will look like the below screenshot.
We can format this calendar by changing pics as per our choice.
You can change the address and contact details in the box available on the right side. Refer to the below screenshot.
Like in the below screenshot, we have mentioned some notes date-wise.
You can customize the calendar using the format tab as per your requirement.
A calendar in Excel is very important in any company, and it uses for different purposes. Here we are taking an example where we will see a way to create a calendar in Excel for a team.
Use Custom Calendar sheet in Excel.Let’s consider two employee data in a calendar in the Excel sheet. David & Ian are two employees in a team, as shown below:
Thus, we are creating a monthly Excel calendar for them. Let’s write the first date of the Jan 19 month in Cell B1. Now, in Cell C1, we will apply the formula =B1+1 for the next date, as shown in the screenshot below.
Hit the enter key and drag this formula in the next columns for the last day of the month. Here drag till the 31st Jan 19.
Copy the same dates in Row 2. Refer to the below screenshot.
It will open a drop-down list of items.
Choose the Custom option from the Category window and write “ddd” under the Type field for Weekdays. “ddd” refers to Mon, Tue, etc. Refer to the below screenshot.
It will convert the dates into weekdays in Row 1.
Again select the whole dates in Row 2 and write “dd” in the Type field under the Custom option. It will convert the dates into days, as shown in the below screenshot.
Select the columns from B to AF and move the cursor on the line between any two columns, and it will compress the cells as shown in the below screenshot.
And our calendar will look like below:
Now our Template is ready. Make a copy of this sheet and give the month name of the sheets, like Jan 2023, Feb 2023, etc. Put the name of this sheet as Template.
For Feb 2023 month, we need to change the date of months like below.
As we can see, the calendar template is ready with us, and now we can use this for scheduling team employee activities date and month-wise.
How to Print a customized Calendar in Excel?In the above section, we learned how to create a calendar. We also can take the print of this. Follow below steps for printing a customized calendar:
Step 1 – Go to the Page Layout tab.
Step 4 – Choose 1 page under Width and Height boxes in the Scale to Fit, As shown in the screenshot below.
Now your customized calendar is ready to print on one page.
Things to Remember
Using this Excel Calendar, you can easily keep track of daily, weekly, and monthly activities, schedules, etc.
You can keep track of your business growth by watching the progress on a fixed-date timeline.
You can keep real-time watch and visibility into the business’s status, resources, and performance.
Recommended ArticlesThis has been a guide to Calendar in Excel. Here we discuss how to use a pre-built calendar in Excel, a Custom calendar, and print the customized calendar along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
Update the detailed information about How To Perform Spell Check In Excel (Examples) on the Cancandonuts.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!