DEV Community: Andrews

How to Use Excel MONTH Function?

Andrews — Thu, 13 Apr 2023 12:43:58 +0000

MONTH

Generally, a month is a unit of time used with calendars. And we all use Calendars for scheduling any events in our lifetime. In Excel, we can print the numerical value of the particular month based on the given date. For that, it offers a special function called “ MONTH.” With the help of this function, we can get the month component of a date as a number.

In this article, we will let you understand the simple syntax, explanation, and examples of the Excel MONTH function. Get an official version of MS Excel from the following link:

https://www.microsoft.com/en-in/microsoft-365/excel

Use Excel Month Function

What is the MONTH Function?

MONTH is one of the built-in functions in Microsoft Excel. It comes under Date and Time functions. We can use this function in our spreadsheet whenever we want to print the corresponding numerical value of the month based on the given dates.

Syntax:

=MONTH(serial_number)

Arguments

*serial_number – * It is the valid Excel date.

Motive:

It extracts the month as a number between 1 and 12 from the given date.

Return Value:

It returns a number between ** 1** and 12.

What Will MONTH Function Do?

As we said earlier, the MONTH function returns the valid number between 1 and 12 based on the given date. This function is categorized as “ Date & Time ” in Excel. In order to use this function, we can give a valid date as input in the cells of Excel.

For instance, we have come up with meeting dates for the organization. For that, we have listed a set of dates in a table. In order to get the numerical value of the given dates, we can use this function.

Meetings date

For that, we need to apply the following formula.

=MONTH(B3)

Enter MONTH formula

Now, *click * on the *Enter * button to execute it and get the result.

Result

To fill down the remaining cells, we just need to drag down the first cell. It will automatically refill the below cells one after the other.

Drag down cell

We hope that the given scenario helped you to understand the MONTH function. If still you have any confusion, then move to the below section, where we will show you a simple example.

Alternate Examples

Let’s see a simple example to know how to use the MONTH function in the spreadsheet.

The following screenshot has shown the interview dates scheduled for an HR. We have given the date value for each newbie. There are a total of 8 students.

Input Values

In order to get the corresponding month number, we need to apply the following formula.

=MONTH(C3)

Type the formula

Once you have entered the formula, *hit * the *Enter * button to get the result, as shown in the below screenshot.

Type the formula

To fill the remaining cells, you need to drag down the first cell. It will automatically fill the below values in the range.

Fill down the values

By doing so, you can easily get the specific month value in Excel. Hope it is easy for you.

Citations

We have referred to the below-mentioned articles to fully understand the purpose of the Excel MONTH function. They helped us a lot. You can also check out them using the given links: MONTH function^[1], and MONTH in Math*^[2]*.

Wind-Up

With the help of the given scenarios and examples, you can easily understand the purpose of the MONTH function in Excel and how to apply it in the spreadsheet. The screenshots help to learn it even easier. Share your suggestions in the below comment section. Thanks for visiting AawExcel. Keep Learning!!

If you want to know what people think about this MONTH Function of Excel, then we suggest you look for it on the Quora interaction webpage. From where you can get to know more regarding the Excel MONTH function. Here, we have provided the link to Quora’s official website.

Video Tutorial

The following video will show you how to use the Excel MONTH function in the spreadsheet with proper examples.

Read Ahead:

Excel Formulas to Calculate Percentile IF in Excel table!!

Andrews — Mon, 15 Nov 2021 07:04:53 +0000

In this tutorial, we will look at how to calculate Percentile IF in the table in Excel Office 365. We will show you the easy steps for completing the task quickly. Utilize the below-mentioned formulas!!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel.

Generic Formula

You can use the below-mentioned formula to calculate Percentile IF in the table.

=[PERCENTILE](https://geekexcel.com/how-to-use-excel-percentile-function-in-office-365-with-examples/)(IF(criteria,values),k)

Syntax Explanation

Parenthesis () – The primary purpose of this symbol is to group the elements.
*Comma symbol (,) *– It is a separator that helps to separate a list of values.
*PERCENTILE *– This function returns the Kth percentile of the given set of data.

Practical Example

For instance, you need to create sample data in Excel.

Sample data

Then, you have to calculate the Percentile 90 % for males and females using the formula below.

=[PERCENTILE](https://geekexcel.com/how-to-use-excel-percentile-function-in-office-365-with-examples/)(IF(Table[Gender]=G$4,Table[Score]),$F5)

Use the Formula

Now, you will get the result as given below in the Excel.

Result 1

After that, you need to calculate the Percentile 80 % for males and females using the formula below.

=[PERCENTILE](https://geekexcel.com/how-to-use-excel-percentile-function-in-office-365-with-examples/)(IF(Table[Gender]=G$4,Table[Score]),$F6)

Use the Formula

Then, you will get the result as given below in the Excel.

Result 2

Now, you have to calculate the Percentile 70 % for males and females by using the formula below.

=[PERCENTILE](https://geekexcel.com/how-to-use-excel-percentile-function-in-office-365-with-examples/)(IF(Table[Gender]=G$4,Table[Score]),$F7)

Use the Formula

Finally, you will get the result as given below in the Excel.

Result 3

From this tutorial, we described the formulas to calculate Percentile IF in the table ** in Excel Office 365. Hope that this article is helpful to you. If you have any **doubts/queries, feel free to share them with us. Thank you so much for Reading!! Keep learning on Geek Excel !! ** And Excel Formulas **!!

Further Reference:

How to Change Text Case in Excel?

Andrews — Fri, 12 Nov 2021 14:33:00 +0000

Change Text Case in MS Excel:

In Microsoft Excel, there is no shortcut for changing the text case of data. But luckily, there are some built-in functions like UPPER , LOWER , and PROPER Functions available in it. You can use this built-in formula to change the text case in Excel 365. In this tutorial, we will learn some easy and quick steps to Change the Text Case of specific data in Excel.

How to Change Text Case with Excel Formulas?

The following three Excel Formulas are used to change the Text Case of any data in a spreadsheet.

Lower Function
Upper Function
Proper Function

Lower Function to Change Text Case:

To Change the Text case of data into Lower Case, You need to use the following formula.
” =LOWER(Text) ” – It takes one argument to change it as Lower Case.
Refer to the next example image.

Upper Function to Change Text Case:

This formula is used to change the data into UPPER Case.
” =UPPER(TEXT) ” – It takes one argument to change it as Upper case.

Proper Function to Change Text Case:

This Function changes the text into the proper case, which means a word starts with a capital letter and is followed by small letters.
” =PROPER(TEXT) ” – this Formula is used to change the data into Proper Case.

Changing Text case using Flash Fill in Excel:

Flash Fill is another simple method to Change the Text Case of the data.
To do this, you need to enter the data with mixed cases in one column, and then you have to give the example data that you want to see as a result on the right of your original data.
To enable Flash Fill for the selected data, you have to press CTRL + E keys, and Now it will repeat the pattern for the remaining rows to fill the changed text case data.

Flash Fill is used for simple data transformation like Changing the Text Case into Upper , Lower , and Proper.

How to Change Text Case using Power Query?

You can use Power Query Options to Change the Text Case of the data. The categories in the Power Query function are:

Text.Lower Function
Text.Upper Function
Text.Proper Function

Text.Lower Power Query Function:

Select the Data Tab on Ribbon Command. Select From Table or Range icon to view the Power Query Editor Dialog Box in Excel. In that dialog box, you can apply the Case Transformation for your data.

In Power Query Editor, You need to Select the Column to transform the data into Lower case.
On the Add Column Tab, Click Format option and select the lower case option from the menu.

It will use the Text.Lower Function to transform the data into lower case.
” =Table.AddColumn(#”Changed Type”, “lowercase”, each Text. Lower([Name]), type text ) ” is the formula shown in the formula bar.
Now, it will display the transformed data in another column.

Text.Upper Power Query Function:

The Steps are the same as above but you need to Select Upper Case from the menu list.

It will create a new column with transformed data like below.

Text.Proper Power Query Function:

In this case, you have to Select Capitalize Each Word Option from the Format menu list to Change the Text Case into Proper.

It will create a new column with transformed data like below.

The above examples are shown the steps to Change Text Cases of data into Lower, Upper, and Proper data.

Wrap-Up:

In the above article, you can understand the steps to Change Text Cases using Formulas , Flash Fill , and Power Query Functions in Excel 365 with the best practice examples. Kindly drop your suggestions/queries in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!

How to Add Running Totals in Microsoft Excel

Andrews — Fri, 12 Nov 2021 14:25:00 +0000

Add Running Totals in Excel:

This article will guide you to know the simple ways to Add Running Totals in Excel 365. Running Totals get changed each time new data is added or modified in the table in Excel. Let’s step into this article and see the ways to Add Running Totals in Excel.

Calculate Running Totals with a Simple Formula:

Here we are going to see how to calculate Running Totals with a Simple Formula using + Operator. For example, we calculate Running Totals based on one person’s daily expenditure. The following table shows the details of everyday spending.

To calculate Running Totals , for the above table. It would help if you used a simple formula that is:

On the first row in the total column, you have to give ” =b2 “. Press Enter to get the result.
Then, you have to enter ” =c2 + b3 ” in the second row of the total column. Then Click Enter to get the result.
You can easily calculate the ** Running Totals** for the remaining rows in the table by dragging them to the last cell in the total column. It will apply the formula for all the cells and calculate the Running Totals.

If we insert or delete rows in the table, some changes will happen in the running totals.

Inserting a new row will leave a gap in the table. You can fix this by copying the formula down from the first cell above the newly inserted row and dragging it to the last row to calculate Running Totals.

If you delete any row in the table, it will show #REF! Error. You can fix it by copying the formula down from the last error-free cell to the last cell in the table.

Calculate Running Totals with a SUM Formula:

Instead of using ** the ” + “** Operator, you can use SUM Formula to calculate Running Totals Easily.

You can enter the formula ” =SUM(b2 + c1) ” below the text cell in the column. SUM Function will consider any text in a cell as ** 0**.
Then Drag this formula to the last cell in that column to get the Running Totals.
If you want to insert/delete rows in the table, you can see the same errors. You can fix this by doing the same method you did in ** calculating Running Totals** with a Simple Formula.

Calculate Running Totals with a Partially Fixed Range:

Here we will use the SUM Function ** to reference the **Expenditure column and use Partially Fixed Range to calculate Running Totals.

To do this, you need to enter the formula ” =SUM($b$2:b5) ” to calculate running totals in the table with a partially fixed range.
If you insert/delete rows in the table, you will have the same problem that happened before. Use the same ways to fix it.

Verdict:

In this article, you can understand the simple ways to Add Running Totals in Excel 365 with examples. Kindly share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

How to Convert Excel Files to PDF in Microsoft Excel?

Andrews — Fri, 12 Nov 2021 05:07:00 +0000

Convert Excel Files to PDF:

MS Excel is one of the most popular software used worldwide to perform complex mathematical calculations, handle the vast data set, and store the data for prolonged use. Here we come up with a built-in feature in Excel that converts files into PDF. In this tutorial, we are going to learn how to Convert Excel Files to PDF Format.

Save the File As PDF:

This method is the most common among Excel user to Save the File as a PDF.
To do this, you need to go to the File tab, select Save As , or Save as Copy option.
Select the File type as PDF.
Click the Save button.

We will save your new PDF file in the exact location as the Excel file saved before.
If you want to access other ** advanced PDF file options** for the converted file, you can use the Options button in the Save As dialog box.
It will open the menu, and you can move the PDF file to the new location.

Export a File As PDF:

You can Use Export Option in Excel to ** export a file as a PDF**.
To do this, Go to the File tab, select the Export option from the menu.
Select the *Create PDF/XPS Document * option.
Then, click the Create PDF/XPS button.

It will open publish as PDF or XPS dialog box.
It is the same as the “Save As” menu, and you can choose the location to publish.

Publish As PDF Quick Access Toolbar Command:

You can Add Publish As PDF option ** to Quick Access Toolbar Command**. So that you can easily access this option.
To do this, Right-Click on the Quick Access Toolbar command and select Customize Quick Access Toolbar from the menu.
It will display the Excel Options Dialog box.
Choose Quick Access Toolbar from the list. Then Select All Commands option.
Now, Select Publish as PDF or XPS option.
Press Add button to add this command to Quick Access Toolbar.
Hit the Ok button.

Now, you can use this command on the Quick Access Toolbar to Publish the Excel File as a PDF.

E-mail As PDF Attachment Quick Access Toolbar Command:

It is one of the attractive options in Excel because it will convert the file as a PDF and then create a new E-mail with this PDF attachment.
If you want to E-mail any file as PDF, you can make use of it.
To do this, ** Right-click** on the Quick Access Toolbar and select the Customize Quick Access Toolbar option from the list.
It will display the ** Excel Options** Dialog Box.
Now, Select the Quick Access Toolbar tab from the menu.
Select ** All Commands** and then Choose E-Mail as PDF Attachment option.
Click the Add button, then hit the Ok button.

Now, we will add it to the Quick Access Toolbar. You can easily use this option from the Quick Access Toolbar.

When you click this icon, it will create an E-Mail outlook with the subject line of your workbook name.

Bottom Line:

In this tutorial, you can understand the simple ways to Convert Excel Files to PDF in Microsoft Excel 365. Kindly share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

How to Add Leading Zeros in Excel?

Andrews — Fri, 12 Nov 2021 04:23:00 +0000

Add Leading Zeros in Excel:

Excel permanently removes the Leading Zeros from any numbers automatically. To overcome this problem, you need to know how to Add Leading Zeros in Excel 365. In this article, you will learn some quick and straightforward ways to Add Leading Zeros in Excel. Let’s step into this article and know the methods.

Change Format as Text:

You can change the format of your data into Text. So that Excel will treat any data you enter as Text data.
If you enter the numbers, it will take it as a text by enabling this option.
To Change the format from general to Text, you need to follow the below steps.

Select the cells you want to give input with leading zeros.
On the Home tab, in the Number Section, Click the Format Drop-down Option.
Select Text from the menu.

Now, you can enter the leading zeros in your data, and it will not disappear because you changed the format as Text. So it will take the leading zeros as text values.

Add Custom Format:

You can add a Custom *Format * to format numbers with leading zeros.
To add a Custom Format, Select the ** range of cells** that you want to add leading zeros.
You have to open the Format Cells dialog box , Right-Click on anywhere in the spreadsheet and click Format cell, or press CTRL + 1 keyboard shortcut to open the dialog box.
On the Number ** tab, Select **Custom Category from the menu.
Now, you need to add a Custom Format in the Type option. If you want to add 6-digit numbers, including leading zeros, give six zeros in the input box.
Hit the ** Ok** button.

Now, you can see the selected cells with leading zeros. If you want to copy and paste this values, leading zeros will disappear.

Leading Apostrophe:

You can Add Leading Zeros with the help of (Apostrophe) ‘ added in front of your data.
Excel will consider the data after the apostrophe is text data, not numbers.
When you click Enter , the added leading zeros will stay visible at the cells. But ‘ will not be visible in the spreadsheet. It is visible only in the Formula bar when the cells are selected.

Text Function:

You can Add Leading Zeros using Text Function in Excel.
To do this, first, you need to enter the data in the spreadsheet.
Then, you have to apply the Text Function formula to add leading zeros.
” =TEXT( Value, Format ) “uses this function in the formula bar.
Value – This is the value you want to convert as a text.
*Format * – Define how many digits you want to use, and add zero for each number.

In the above example, you can see the Format of Text Function, “=TEXT(C4,”000000″)” and it will add leading zeros in the Text Function column.

Right Function:

You can also use this Right Function to Add Leading Zeros to your data.
Right-Function will get the rightmost text characters from your data.
” =RIGHT( Text, [Number] )” this is the formula you need to enter in the Formula bar.
Let’s see the example.

The above image has shown how to Add Leading Zeros with the Right Function.

Base Function:

You can also use the Base Function to convert the data as text data.
“=BASE ( Number, Base, [Min Length] )” this the Base Function formula.
Number – In this section, you must give the input data you want to convert as text.
Base – This is the base you want to convert the data to.
Min Length (optional) – This is the minimum length of your text data.

In the above image, you can see the Base Function structure in the formula bar. Apply this function to convert the data as Text.

Power Query and Text.Pad Start Function:

Generally, Power Query is used for data transformation, So it’s one of the solutions for Converting data into Text.
To do this, you need to select the data and Open the Power Query Editor Window.
After inserting your data into Power Query, you need to add another column using this formula ” = Text.PadStart ( [Number], 6, “0”) “
Then, On the Add Column tab, Select the Custom Column option.
You have to create the new name for the custom column, add the above formula on the Custom Column Formula box.
Hit the Ok button.

It will create a new column with data. All data is added with leading zeros.

Bottom Line:

In the above article, you can understand the Steps to Add Leading Zeros in Excel 365 with practical examples. Share your suggestions/queries in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!

Concatenate Data with a LINE BREAK in Excel [ 5 Steps ]

Andrews — Thu, 11 Nov 2021 14:28:00 +0000

Concatenate Data with a Line Break:

Here we come up with an exciting article to Concatenate Data with a Line Break in the data in Excel. We can easily combine the data from different cells in the spreadsheet into one cell and discrete them using line breaks. Let’s see the steps to Concatenate Data with a Line Break in Excel.

How to Concatenate with a Line Break by using the Ampersand Operator?

It is the most commonly used method to ** Concatenate with a Line Break using the Ampersand** Operator.
This & operator can concatenate the two or more cells into one cell.
CHAR(10) function will return a line break on Windows. So here, we use this function for adding line breaks in the data.

You can enter a Formula like the above example image.

How to Concatenate with a Line Break by using CONCATENATE or CONCAT Function?

We can Concatenate data with a line break using either Concatenate or CONCAT Function.
It is the same as the first method. We can use Concatenate or CONCAT Function in the Formula to discrete the data with line breaks.

Like the above example image, you can include the formula to Concatenate strings in different cells into a single cell.

How to Concatenate with a Line Break by using TEXTJOIN Function?

Use TEXTJOIN Function to Concatenate with a line break. This method is proper when you want to discrete more cells into one.
This method is easy to Concatenate the range of cells with a line break.
To do this, you need to enter the formula using *TEXTJOIN * like below.

In this method, we need to use the CHAR function only once.

How to Concatenate with a Line Break by using Power Query?

Power Query is an excellent tool in Excel. It allows you to import data from many sources and transform and reshape the data.
Using Power Query, you can concatenate data with a line break into a single cell. To do this, you need to follow the below steps.

Select the table and On the Data tab, choose From Table/Range Icon.
It will open the Power Query Editor Dialog box , in which you need to select the column name ( Non-text data field ) and Left-click on it, then choose the text option from the menu list.
If there are any numbers in the data, it will show an error. So it would help if you changed that into Text.
Then, you have to concatenate with Text.Combine Function.
Now, you can combine the data. Click on the Fx icon near the formula bar in the Power Query Editor.
= Table.AddColumn(#”Changed Type”, “Address Labels”, each Text.Combine(Record.ToList(_),”#(lf)”)) type this formula in the formula bar. Then Click Enter key.
**“#(lf)” **is a Power Query Line Break Character.
Now, you can Close and Load the data using the Home tab.

If you feel this method is easy, then make use of it.

How to Format the results with wrap text?

The above steps are used to Concatenate Data with a Line Break. But you still see the results.
Let us give you the final step to see the result as you wish.
All the Line Break Characters we include in the data are there. Now you need to format the cells to wrap text to see the results.
Select any cells in the table that you want to format. Right-click on it and select the Format Cells option from the menu list.
On the Alignment tab, Check the Wrap Text box and click the Ok button.

Now, the selected cells are displayed in multiple lines.

Added Information:

Press CTRL + 1 Shortcut to open the Format Cells Dialog box in Excel.

A Short Synopsis:

In the above article, you can learn the steps to Concatenate Data with a Line break in Excel. If this content is helpful to you, share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

Remove Unwanted Space Characters in Excel!

Andrews — Thu, 11 Nov 2021 13:36:00 +0000

Remove Unwanted Space Characters in Excel:

In this article, we will learn the easy ways to Remove Unwanted Space Characters in the data in Excel 365. While working in Excel spreadsheets, some unwanted space characters are included. These unwanted space characters should be removed from the data so that only we can get valid data. Here are some examples of Unwanted Space Characters in the data table in Excel.

How to Remove Unwanted Spaces using TRIM Function?

The TRIM Function removes all the extra spaces from the data in Excel except a single space between two words.
For example, you need to enter ” =TRIM(B2) ” or “ TRIM(“Keep Learning!!”) ” to remove the extra spaces.
Refer to the below example image.

You can use Function Arguments Dialog Box to remove extra spaces.
In that dialog box, you have to enter the cell address and Click the Ok button.
It will give the result without Extra space.

How to Remove Unwanted Spaces between words Using Find and Replace?

This method is applicable only for ** Removing Unwanted Spaces Between Words** in Excel with the help of the Find and Replace Option.
To do this, you need to ** select the range of cells** you want to remove unwanted spaces.
On the Home tab, in the Editing section, Select Find and Select Option and click Replace Option from the list.
Now, it will display the Find and Replace Dialog Box like below.

You have to choose the Options button to view the more advanced ** Find and Replace** options in that dialog box.
In Find what option, you need to give two space characters by pressing the Space bar twice on the keyboard.
Replace with an option, and you need to add a single space character by pressing the Space bar once on the keyboard.
Check Within option selected sheet , ** Look in** option selected Formulas.
Now, press Replace All button to Remove the Unwanted Space Characters.

How to Remove Unwanted Spaces with Power Query?

Here we will follow some set of instructions to ** Remove Unwanted Spaces Using** Power Query in Excel.

On the Data tab, you have to select the From Table or Range command.

After clicking that icon , it will display the Create Table Dialog box. You need to select the range of cells and press the Ok button.

Now, it will display the Power Query Editor Dialog Box. Then you need to Right-click on the column heading. It will display the menu list.
From that menu, you have to select the Transform option, then Select Trim.

It doesn’t remove the unwanted spaces from that table, so you need to take more steps to remove extra spaces.
To do this, Right-Click on the column name and Select Replace values from the menu list.

In ** the Value To Find** Option, you have to provide two Space Characters as input.
In ** Replace With** Option, you have to provide Single Space Character as input.
Press the Ok button. You have to repeat these steps until extra spaces are removed from the data.
To get the cleaned data back to Excel, you have to close and load the data.
On the Home tab in the Power Query editor, Click the Close and Load option. Then select the table ** and Select the **Location to load the table in Excel.

It will give the result like the above image.

A Brief Summary:

In the above article, you can clearly understand the ways to Remove Unwanted Space Characters in the data in MS Excel 365. Drop your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

FLASH FILL Options in Microsoft Excel 365!

Andrews — Thu, 11 Nov 2021 13:22:00 +0000

A Complete Guide to Know About Flash Fill in Excel:

Excel Flash Fill Option is mainly used to automatically fill the data in the column using the example pattern. In this article, we are going to learn the Flash Fill options in Excel 365.

Versions of Excel have Flash Fill Option:

Excel Flash Fill was released in Excel 2013, and other later versions (Excel 2013, 2016, 2019, and Excel Office 365) have this.
Excel 2010 or any other previous versions didn’t have Flash Fill Option.

Flash Fill in Excel:

You can find Flash Fill Option in two ways in Excel 365.

On the Data Tab in Ribbon Command. In the Data Tools ** Section, **Select the Flash Fill Icon.

On the Home tab, in the Editing Section Select Fill Option, then Click Flash Fill from the menu.

Use Flash Fill in Excel:

Here we are going to see an example of Flash Fill for how to use it.
In the below example, we want to join the first and second names of a person and city names to create an E-mail Id.
We give the pattern to Excel for the first cell. The remaining cells are filled with the Flash Fill option.

We can use Flash Fill by other options in Excel. Let us see one by one.

Automatic Flash Fill:

Flash Fill will automatically fill results with the help of the few examples we have given.
Excel then shows a preview in light gray color, and then you have to *press Enter * key to see the results.
Flash Fill cannot fill upward cells in the column, and it can only fill the downward cells from the example.

Flash Fill Command in the Ribbon:

If you want to activate the Flash Fill option manually, you need to know the following instructions.

You need to ** type the example data** that you want to be returned.
Select the cells you want to fill and the cells you have given examples.
Then, go to the Data tab. In the Data Tools section, select the Flash Fill Command.
Don’t forget this command also found on the Home tab, in the Editing Section Choose Fill Option and Select Flash Fill Option from the menu.

Flash Fill Keyboard Shortcut:

In Excel, everything has shortcut options. So Flash Fill also has a shortcut key.
You have to do some actions before using Shortcuts.
Type the example data which you want to be returned.
Select both the cells you want to fill and cells with examples.
Now, press CTRL + E on your keyboard.

Flash Fill from the Fill Handle:

We will use flash Fill from Fill Handle.
To do this, Select the example cells and Right-Click on it, then drag the Fill Handle Down. Then release right-click from a mouse.
Choose Flash Fill Option from the menu.

Add Flash Fill to the Quick Access Tool Bar:

Right-Click on the ** Quick Access Tool Bar** or the Ribbon to view the Excel Options Window.
Click the Quick Access Toolbar from the menu.
Select All Commands and Choose Flash Fill from the commands.
Hit the Ok button.
Now, We will add the Flash Fill Icon to the Quick Access Tool Bar.

Enable or Disable Flash Fill:

You can disable Flash Fill Option in Excel.
To do this, you need to go to the File tab on the Ribbon. Select Options from the menu.
Then Select the Advanced tab; uncheck ** the Automatically Flash Fill** option from the Editing Options.
Hit the Ok button.

Still, you will use Flash Fill with the help of Ribbon Command and Shortcut.

Practical Examples for Flash Fill in Excel:

Let’s see some set of examples for Flash Fill, and it will help you understand clearly.

Extract the Last or First Name from a Full Name:

You can easily ** Extract the Last or First Name** from a full name.
For example, we will Extract the First Name from a full name using Flash Fill like the below.

Concatenate First and last Name to Create a Full Name:

The following example uses Flash Fill to Combine the First and Last Names into a format.

Convert Names to Initials:

Flash Fill can ** extract the Initials** from the first and last names.
If you have separate first and last names, they will be combined and removed the initial from it.

Change Any Text to Upper Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for changing the text to Upper Case.

Change Any Text to Lower Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for changing the text to Lower Case.

Change Any Text to Proper Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for Change the text to Proper Case.
The excellent case will change the Mixed Case of data.

Extract the Company Name from an E-Mail Address:

You can get a Company Name from the E-Mail Address using Flash Fill.

Extract the Name from an E-Mail Address:

The following example shows that you can Extract Name from the E-mail Id.

Create an E-Mail Address from the list of First and Last Name:

This example shows you how to create an E-mail address in the format .@companyname.com using Flash Fill Option.

Extract the Number from Any Text String:

If your text contains a number, then you can extract the number from the text strings using Flash Fill.

Extract the Letters from Any Text Strings:

If you want to extract the text part of your data, then use the Flash Fill method to do that.

Format Numbers As Phone Numbers:

The following example uses Flash Fill to convert 10-digit phone numbers to (###) ###-#### format.

Remove Excess Space Characters:

The following example shows you to remove extra space characters ( from the start, middle, and end of the text ) using Flash Fill.

Convert Text to Date Values:

Flash Fill can convert text strings into date values.

Options Button in Flash Fill:

After using Flash Fills, a floating options button appear at the end of the column. If you click this button it will show some options.

Flash Fill Error:

If the Flash Fill cannot determine any pattern, it will show like the below image.

Remedies when Flash Fill shows this Error:

Go through your example and correct any errors. A small spelling mistake can result in Excel failing to find a pattern.
Provide more examples for Flash Fill. It may need a few more examples to find the correct way.
If the Flash Fill option doesn’t run, it may be disabled. Then Check the Excel Options menu to see whether the automatically run flash fill option is enabled or not.

Limitations of Flash Fill:

The results are not dynamic.
Flash Fill may not always return the result. The pattern can be too complex for Excel.
Flash Fill does not fill the results horizontally, and it can only fill results vertically.

End of the Article:

In the above article, you can understand how to use Flash Fill and practical examples and their limitations. Drop your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

Get the Weekday Name From A Date in Excel!

Andrews — Thu, 11 Nov 2021 00:21:00 +0000

Get the Weekday Name From a Date in Excel:

Sometimes we want to know the Weekday Name just because we forget that. So there are ways to Get the Weekday Name from a Date in Excel. Let’s step into this article and see how to ** Get the Weekday Name from a Date in Excel 365**.

How to Format a Date as the Weekday Name?

First, you are going to see how to format the date cells.
Excel considers the date as serial numbers that start from 1 for the date 1900-01-01. You can format the date like yyyy-mm-dd, dd/mm/yyyy, dd-mm-yy, etc.
Formatting options to these serial numbers display the Weekday name with custom format (dddd or dd).
To do this, select the dates which you want to change into weekday names.
On the Home tab, click the launch icon at the corner of the numbers section. It will open the ** format cells** dialog box.
In the Format Cells dialog box, On the Number tab, select Custom as Category.
Enter dddd in the type field for the full weekday name or enter DDD for the abbreviated weekday name.
Hit the Ok button.

Now it will display the Weekday Name in the Selected Cells according to the date you entered.

How to Get the Weekday Name using TEXT Function?

You can use the TEXT Function to convert any date into text strings using the Weekday Name Format.
“ = TEXT ( Value, Format ) ” it is the syntax for TEXT Function.
For example, you need to enter this formula in the Formula bar like ” =TEXT ( B2, “dddd” ).
It will convert the Date into Weekday Name.

How to Get the Weekday Number using WEEKDAY Function?

You can also use the WEEKDAY Function to get the Weekday Number.
” =WEEKDAY ( Date, [Type] ) ” is the syntax for WEEKDAY Function.
Date – the date to find the weekday number.
Type – the weekday number type to return.

For example, here we enter this formula as “** =WEEKDAY( B2, 1 )** ” this formula will calculate the date value into the corresponding weekday value.
The second argument will return 1 for Sunday through to 7 for Saturday, and it returns 6. So the Weekday for this date value is Friday.

Combining Switch with WEEKDAY to return the Weekday Name:

The WEEKDAY function only returns the number corresponding to that weekday name.
You can Combine Switch Function to get the WEEKDAY Name.
Use the following syntax of Switch Function to assign a Weekday Name to each number in the formula.
” = SWITCH ( WEEKDAY (b2, 1), 1,”Sun”, 2, “Mon”, 3, “Tue”, 4, “Wed”, 5, “Thurs”, 6, “Fri”, 7, “Sat” ) “

How to Get the Weekday Name using Power Query?

As we know, Power Query is used for data transformation, so We might use it for converting the date value into Weekday Name.
Select the cells that contain the date inside it.
On the data tab, Select From Table/Range ** option. It will open the **Power Query Editor Dialog box.
In Power Query Editor, select the Date Column. ** Then go to the **Add Column tab, Select Date Option, it will display the menu.
Choose Day from the menu and select the Name of the Day option.
It will display the WEEKDAY NAMES for the corresponding date you given in the date column.

You can see the following code that is generated automatically in the formula bar.
” = Table.AddColumn(#”Changed Type”, “Day Name”, each Date.DayOfWeekName([Column1]), type text) “.
It uses the Date.DayOfWeekName Function to find the weekdays.

Wind-Up:

In this post, you can quickly learn the steps to ** Get the Weekday Name from a Date** in Excel 365. Kindly, Leave your feedback/queries in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!

EXCEL TABLES- A Walk Through of using Tables in Excel!

Andrews — Wed, 10 Nov 2021 14:50:00 +0000

Excel Tables:

Generally, Excel tables are used to manage and analyze the huge data in spreadsheets. It can convert a list of data into the formatted Excel table to make our work easier. It has many features like formatting, editing, filtering, and sorting. These features help us to organize the data.

Let’s jump into this tutorial and learn how to create the Excel Tables magnificently using various options provided in Excel 365.

How to Turn the Data into a Table?

First, you need to enter the data in the Excel spreadsheet to create a table.

For Example, Here, we are going to create a table with Employee details. To do this, you have to include the details like Employee id, name, age, salary below.

Employee Details

Now, you have to turn the data into a table.
Select any cell in the data range.
On the ** Insert** tab, select Table under the table selection command.
Check if your entire data range is selected or not.
Check My Table has a headers box.
Hit the Ok button.

Now, the data are turned to the table format and it will give the result like below.

How to Name and Style the Table?

To add name and style to the table, follow these steps:

Go to the Table Design tab. ( this tab is visible when the cursor is on the table cells )
On the Properties tab, type the table name and click Enter key.
On the Table Styles tab, select any style and click Ok.

Name and style the table

That’s it, and we have given the name and style to the formatted table.

How to Add Summary Statistics to the Table?

Here we are going to see how to add summary statistics to the table. Summary statistics are beneficial to reduce our manual work because you don’t need to type individual functions like sum, mean, average. So we can easily make these kinds of calculations.

To do this, you need to follow the instructions given below.

On the Table Design tab, Check the Table Row option under the Table Style Options.
At the bottom of the Total table, the row has been created. Select any summary formula from the Drop-down List as per Your wish.

Add summary statistics to the table

How to Sort and Filter the Table?

To Sort and Filter the data in the table, you have to do the following steps.

On the Table Design tab, check the Filter Button.
Sort and Filter Toggles will add to each heading in the table.
Click the Toggle in any one of the headings to use the Sort and Filter Menu.

Sorting and filtering the table

Note:

You can use CTRL + T shortcut keys to create a table in Excel.

Closure:

This tutorial clearly understands the Steps to Create a Table in Excel 365 using various options. Kindly share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

How to Use Formula Bar for Various Purposes in Excel

Andrews — Wed, 10 Nov 2021 14:46:00 +0000

Formula Bar in Excel:

In this tutorial, we are going to learn about Formula Bar in Excel 365. The Formula Bar in Excel can store the values and formulas in it. It is a significant working field in Excel because it performs unique and complex mathematical calculations.

How to Expand or Contract the Formula Bar in Excel?

We can expand the Formula Bar vertically or horizontally.

Fix the mouse cursor between the name box and the formula ba r to expand the Formula Bar horizontally. Now it will show the double-ended arrow. It would help if you right-clicked in the mouse and drag to adjust the formula.

To expand the Formula Bar vertically , move the mouse cursor to the bottom of the formula bar. Now, it will change into a double-ended arrow. Then right-click in the mouse and drag to adjust the formula.

Expand or Contract Toggle is on the right side of the formula bar. We can use this to adjust the formula bar quickly.

How to Hide or Show the Formula Bar?

We can hide or show the Formula Bar by using this Formula Bar option in View Tab on the Ribbon Command.

How to Enter and Edit the Data in Formula Bar?

To Enter or Edit the data into any cell in the worksheet, follow these steps:

Select any cell in the worksheet and start typing the data.
When you type the data, it will appear at the Formula bar.
If you want to accept the data, click the Checkmark, or click *Enter. *
If you’re going to discard it, then click the *X * or press the *Esc * button.

Note:

If you want to enter a formula in Formula Bar, you need to start with ” = ” ( equal to ) Symbol.

To Edit any data or formula in the specific cell, you have to click into the formula bar and edit the cell or click F2 to apply editing mode.

How to Insert Function Dialog Box?

The Function dialog Box shows the available functions in Excel.

Select a cell to enter a function.
Click the Insert Function icon at the Formula Bar.
Select a Function using the Drop-down menu based on some category.
View the brief description to know about that Function.
Click the Help on this function option to learn more about that Function.
Hit the Ok button.

Function Arguments Dialog Box:

After clicking the Ok button in the Function dialog box , It will display the Function Arguments Dialog Box below.

- In this dialog box, you have to enter the arguments (1) for your function, and it will appear on ** the right side** of the input boxes (2).
- You can see the description of that logical test below the input boxes (3).
- Then you can see the given Formula ** at the Formula Bar **(4).
- Click Help on this function option to know more about that function (5).
- Finally, click the Ok button (6) to see the result in the active cell.

Verdict:

In this article, we guided you to learn about ** Formula Bar ** options in Excel 365. Kindly drop your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!