This post will guide you how to change the font color based on cell value in Excel. How do I color cell based on cell value using the conditional formatting feature in Excel.
1. Changing Font Color Based on Cell Value
Assuming that you have a list of data in range A1:C6, and you want to change the font color based values in A1:C4 cells, if the value is greater than 5, then changing the font color as red, otherwise, changing the font color as green. To changing the font color based on cell value, you need to do the following steps:
#1 select the range of cells which contain cell values, such as: A1:C6.
#2 go to HOME tab, click Conditional Formatting command under Styles group, and then click New Rule from the drop down men list. And the New Formatting Rule dialog will open.
#3 In New Formatting Rule dialog, select Format only cells that contain in the Select a Rule Type list box, chose Cell Value from the first list box, and select greater than from the second list, type number 5 into last text box under Format only cell with section.
#4 click Format button, and switch to Font tab in Format cells dialog, and choose one color as you need in Color list box. Click OK button.
#5 click Ok button. The Font color has been changed as red if the cell value is greater than 5 in the selected range of cells.
#6 repeat step 3, and create a new rule, select less than from the second list , and type number 5 into last text box under Format only cell with section. And click Format button, choose green color in Color list box. And click OK button.
#7 let’s see the last result:
2. Video: Changing Font color Based on Cell Value
This video will demonstrate you how to chagne Fond colore based on Cell value using Conditional formatting feature in Microsoft Excel 2013/2016/2019/365.
Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any way to split one cell to only two cells by the first space in the texts? In this free tutorial, we will share you some useful functions in excel to split cells per your demand.
Precondition:
In product list we use space to separate product properties.
Now we want split it to two parts in two different columns by the first space. See expectation below:
1. Split Cells to Two Columns by the First Space in Texts by Formula
Step5: Click Enter to get result. Verify that texts are displayed properly.
Step6: Drag the fill handle down till the end of the list. Now texts are split to two columns by the first space properly.
2. Split Cells to Two Columns by the First Space in Texts with VBA Code
You can use a VBA Code that prompts the user to select a range of cells and a destination cell, and then splits the cells in the selected range to two columns based on the first space in the text.
Step1: Press Alt + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, go to “Insert” on the top menu and select “Module“.
Step3: Paste the below VBA code into the new module.
Sub SplitCellsByFirstSpace_ExcelHow()
' Prompt the user to select a range of cells
Dim selectedRange As Range
Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
' Prompt the user to select a destination cell
Dim destinationCell As Range
Set destinationCell = Application.InputBox("Select a destination cell:", Type:=8)
' Insert the formula to split the text at the first space
For Each cell In selectedRange
cell.Offset(0, 2).Formula = "=RIGHT(" & cell.Address(False, False) & ",LEN(" & cell.Address(False, False) & ")-FIND("" "", " & cell.Address(False, False) & ",1))"
cell.Offset(0, 1).Formula = "=LEFT(" & cell.Address(False, False) & ",FIND("" "", " & cell.Address(False, False) & ",1)-1)"
Next cell
' Copy the formula to the destination cell and adjacent cell
selectedRange.Offset(0, 1).Resize(selectedRange.Rows.Count, 2).Copy destinationCell
' Convert the formula to values in the destination cells
destinationCell.Resize(selectedRange.Rows.Count, 2).Value = destinationCell.Resize(selectedRange.Rows.Count, 2).Value
End Sub
Step4: Press F5 to run the macro or click the “Run” button on the toolbar.
Step5: A dialog box will appear asking you to select a range of cells. Click and drag your mouse to select the range of cells you want to split.
Step6: Another dialog box will appear asking you to select a destination cell. Click on the cell where you want to place the split data.
Step7: The macro will split the data in the selected range into two columns based on the first space in the text, and place the split data into the destination cell and adjacent cells.
3. Video: Split Cells to Two Columns by the First Space in Texts
This video will demonstrate how to split cells to two columns by the first space in texts using Excel formulas and VBA code.
4. Related Functions
Excel LEFT function The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
Excel FIND function The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Excel RIGHT function The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
Excel LEN function The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
This post will guide you how to filter values starts with number or letter in one single cell in Excel. How do I filter in a list those that begin with number or letter using a formula in Excel.
1. Filter Cells Starts with Number or Letter
Assuming that you have a list of data in range B1:B4 which contain text string values. Ad you want to filter those cells values which are starting from a number or letter. The below steps will show you how to filter the data which starts with numbers or letters only.
Step1: select one blank cells in the adjacent Cell B1. And enter the following formula based on the ISNUMBER function and the LETF function.
Note: Cell B1 is the first cell in your data which you want to filter out values that starts with number or letter.
Step2: press Enter key to apply this formula.
Step3: then drag the AutoFill Handle in Cell C1 down to other cells to apply the same formula. You would see that if the data starts with a number, then returns TRUE. Otherwise, returns FALSE.
Step4: select the cell C1 in the helper column, go to Data tab in the Excel Ribbon, and click Filter button under Sort & Filter group. And one Filter icon will be added into the Cell C1.
Step5: click filter icon in the Cell C1, and check True to filter out all data that starts with number. Click Ok button.
Step6: let’s see the last result:
2. Video: Filter Cells Starts with Number or Letter in Excel
This video will guide you through using a formula with the ISNUMBER function and Sort & Filter feature to effectively filter cells that start with a number or letter in Excel.
3. Related Functions
Excel ISNUMBER function The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel LEFT function The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
This post will guide you how to Copy a Range of Selection to a Text file in Excel. How do I copy/paste a range from the current worksheet to a notepad text file with VBA Macro in Excel 2013/2016/2019/365.
Assuming that you have a list of data in range B1:B5 in your worksheet, and you wish to copy this range to another worksheet or a notepad text file, and save it as a .txt or xlsx. You can copy the selection to the clipboard by manually, open up a Notebook file or what ever text application you use, or you can open an blank worksheet, then paste it in there and save as the file. This method can work well, but it is not a good way. You can use an Excel VBA Macro to accomplish it quickly. And this post will show you how to use an Excel VBA code to save a range of selection to a text file.
1. Save a Range Selection as Text File
You can use an Excel VBA macro to save the selection as a text file without copying and pasting to a new file. Just do the following steps:
Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step3: click “Insert” ->”Module” to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “Save” button.
Sub SaveSelectionAsTextFile()
Dim myFolder As String
'Save Range as Text File.
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select one Range to be copied", "SaveSelectionAsTextFile", myRange.Address, Type:=8)
ActiveSheet.Activate
myRange.Copy
'This temporarily adds a sheet named "Test."
Sheets.Add.Name = "Test"
Sheets("Test").Select
ActiveSheet.Paste
'Ask user for folder to save text file to.
myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
'Save selected data as text file in users selected folder.
ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Indicate save action.
MsgBox "Text File: " & myFolder & "Saved!"
'Go to top of sheet.
Range("A1").Select
End Sub
Step5: back to the current worksheet, then run the above excel macro. Click Run button.
Step6: Select one range to be copied. Click OK button.
Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.
Step8: open the newly created text file test1112.txt in your local disk.
2. Video: Save a Range Selection to Text File in Excel
This video will demonstrate how to use VBA code to save a range selection to a text file in Excel.
This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once with VBA in Excel.
Assuming that you have created a pivot table with your data. And as the default setting for numbers is to sum the data for all numeric fields and I want to change the count of function to sum of function. You can change each of columns using the value field setting option or you can also use an VBA macro to change all fields at once.
1. Change Multiple Pivot Table Fields by Manually
If you want to change multiple pivot table fields, you can change the function in the Value Fields Settings, just do the following steps:
Step1: select one filed in your pivot table, and right click on it, and then choose Value Fields Settings from the dropdown menu list. And the Value Fields Settings dialog will open.
Step2: select Count function in the Summarize value field by list box, and click Ok button.
Step3: you would see that the sum function has been changed to count function for your selected field.
Step4: you need to repeat the above steps to change other’s fields for your pivot table.
2. Change Multiple Pivot Table Fields by VBA
You can also convert all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel. Just do the following steps to run the macro:
Step1: select your pivot table that you want to convert fields
Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step4: click “Insert” ->”Module” to create a new module.
Step5: paste the below VBA code into the code window. Then clicking “Save” button.
Public Sub PivotFieldsToCount()
Dim pf As PivotField
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlCount
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub
Note: If you want to convert all fields to SUM function, you just need to change “x1Count” to “x1Sum” in the above VBA code.
Step6: back to the current worksheet, then run the above excel macro. Click Run button.
This post will guide you how to limit formula result to a maximum or minimum value in excel. For example, if you want to limit the results of a formula to a maximum value of 200, how to this this in excel. How do I return a formula result only within a specified minimum or maximum values in excel. Do we have any ways to limit the results of a calculation to a maximum value.
You can use the MIN function or Max function to create a formula to limit a number within a minimum or maximum value. Just do the following steps:
1. Limit Formula Result to Maximum Value
If you want to limit a formula result to a maximum value 200, you can use the MIN function to create a formula, just like this:
If the result of SUM function is greater than 200, then the MIN function will return value 200. Otherwise, return the result of SUM function.
2. Limit Formula Result to Minimum value
If you want to limit a formula result (for example, the sum function) to a minimum value, then you can use the MAX function in combination with the SUM function or other function to create a new formula. Just like this:
If the result returned by the SUM function is smaller than 200, then returns value 200, otherwise, it will return the result returned by the SUM function.
3. Video: Limit Formula Result to Maximum or Minimum Value in Excel
This video will demonstrate how to limit the result of a formula to a specific minimum or maximum value using the MIN Function or MAX Function in Excel.
4. Related Functions
Excel SUM function The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Excel MIN function The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
Excel MAX function The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
When pressing F1 key it forth to enable Help pane in excel. In some situations, we can press F1 to enable other actions from other applications, for example, for some tools we can via pressing F1 to capture screen directly. So, we can disable F1 key in some instances. This article will help you to know the method to disable F1 key in excel by VBA code.
1. Disable F1 Key with VBA Code in Excel
Click Help tab in ribbon, and move cursor floating on Help icon. You can see a tip pops up to indicate that F1 is the shortcut key for Microsoft Excel Help. Now let’s disable F1 key refer to below steps.
Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.
Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.
Step2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1:
Sub disableF1()
Application.OnKey "{F1}", ""
End Sub
Step3: Save VBA code. And then quit Microsoft Visual Basic for Applications.
Step4: Try to click F1, verify that Help system is not loaded and displayed.
Comment:
This method only takes into effective on Excel 2007/2010, it doesn’t work on upper versions.
If you want to enable F1 again, enter below VBA code:
This post will guide you how to reverse number signs in Excel. How do I reverse the sign of a range of cells from positive to negative or negative to positive in Excel. How to reverse sign of number with a formula in Excel.
Assuming that you have a list of data in range B1:B4 that contain some numbers, and you want to reverse the sign of those numbers. You can use the below two methods to achieve it.
1. Reverse Number Signs with a Formula
You can multiply all numbers in a range of cells by a negative number 1, so that you can get the expected value. So just type the following formula in a blank cell C1.
=B1*-1
Then you need to drag the AutoFill Handle over other cells to apply this formula.
So the positive number will be converted to negative numbers and the negative number will be converted to positive numbers. Then you still need to copy and paste the last result to the original range of cells.
2. Reverse Number Signs with Paste Special Feature
You can also use the Paste Special Feature to reverse number signs in Excel. Just do the following steps:
Step1: type negative number -1 in a blank cell and press Ctrl+C to copy it.
Step2: select the range of cells that contain numbers you want to reverse the signs. And right click on it and select Paste Special from the popup menu list. And the Paste Special dialog will open.
Step3: select Multiply radio button in the Operation section. And then click OK button.
Step4: you will see that all of the selected cells will be multiplied by negative -1. And all number signs will be reversed.
3. Video: Reverse Number Signs in Excel
This video will show you how to reverse the number signs in Excel using both a formula and the Paste Special feature.
In statistic when calculating the total values for a range of numbers, we often count duplicate values only once. Normally, if only apply SUM function to calculate total values, it will cover all values including all duplicate values. So, we need to update the formula to make it meet our requirement. This free tutorial will show you the formula to sum values with duplicate values only once in the selected range.
Precondition:
See screenshot below. We want to sum all values but for the duplicate values, we only count once.
1. Calculate Total Values with Counting Duplicate Values Only Once using Formula
In this formula, A2:B6 is the range selected for calculating.
Step2: Click Enter to get result. Verify that the sum value is calculated properly. Duplicate values like 100, 60 are only count once.
2. Calculate Total Values with Counting Duplicate Values Only Once using User Defined Function
You can create a User-Defined Function (UDF) in VBA to calculate the total values of a range while counting duplicate values only once. Here are the steps of how to do it:
Step1: press ALT + F11 to open the VBA editor in your workbook.
Step2: In the editor, go to Insert > Module to create a new module.
Step3: In the module, paste the following code. Save the module and go back to your Excel workbook.
Function SumUnique_ExcelHow(ByVal rng As Range) As Double
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range
For Each cell In rng
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, 1
End If
Next cell
Dim key As Variant
For Each key In dict.keys
SumUnique_ExcelHow = SumUnique_ExcelHow + key
Next key
End Function
Step4: In any blank cell, enter the formula:
=SumUnique_ExcelHow(A2:B6)
Where A2:B6 is the range of values you want to calculate the total for.
Note: The User Defined Function loops through each cell in the range and adds its value to the dictionary object only if it doesn’t already exist as a key. Finally, the User Defined Function loops through the dictionary keys and adds up the total value, which is returned as the function result.
3. Video: Calculate Total Values with Counting Duplicate Values Only Once
This video will show you how to calculate total values with counting duplicate values only once using either the SUMPRODUCT function or a user-defined function with VBA code.
4. Related Functions
Excel SUMPRODUCT function The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel COUNTIF function The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
To calculate average with given criteria we can apply AVERAGIFS function. If the criteria or condition is a period for example one or more months, we can provide month information and then calculate average based on the given month. There are some date and time functions to return months or dates can represent a month. In this article, we will show you how to calculate average by month with AVERAGEIFS function, we use logical expressions with EOMONTH function to represent month.
In this article, we will introduce you the syntax, arguments and basic usage of above-mentioned functions, and use them to build a formula, and let you know the calculation steps of the formula.
1. EXAMPLE
Refer to the left-hand side table, we can see some kinds of fruits are listed in “Fruit” column. Numbers in “Amount” column represent the sales on different dates. Dates belong to different months are recorded in “Date” column. In the right-hand side table, we want to calculate the average of amounts based on given months. In Month column, E2 and E3 are actually dates. For the following calculation we enter dates 05/20/2021 and 06/20/2021 into E2 and E3, and they are the earliest date shown in “Dates” column for May and June. But for looking clearly, we just show them in mmm-yyyy format and hide the date part.
To calculate the average of numbers based on given criteria, we need to apply AVERAGEIFS function here. In this case, our criteria is a whole month not a given period with start date and end date, so we can enter “DATE(2021,5,1)” as start date and “DATE(2021,5,31)” as end date. We can also apply EOMONTH function to return the last date of the given month.
Before creating a formula to calculate average value, we can name range references.
Select range B2:B13, in name box enter “Dates”, then press Enter.
Select range C2:C13, in name box enter “Amounts”, then press Enter.
2. CREATE A FORMULA with AVERAGEIFS & EOMONTH FUNCTIONS
You can enter named range like “Amounts” into your formula directly, or you can also select the range by dragging the handle as well.
Step2: Press Enter after typing the formula.
Pick up amounts from May, they are 5000 in C2, 4000 in C3, 6000 in C6, 5500 in C7, 8500 in C10, and 9000 in C11. Total 6 numbers. The average is (5000+4000+6000+5500+8500+9000)/6=6333.33. The formula works correctly.
3. FUNCTION INTRODUCTION
AVERGAEIFS function can be seen as AVERAGE+IFS. It returns the average of some numbers in range based on one or more given conditions or criteria.
For AVERAGEIFS functions, it can handle wildcards like asterisk ‘*’ and question mark ‘?’; it also supports logical operations like ‘>=’,’<=’. If we need entering wildcards or logical operators to build criteria, they should be enclosed into double quotes (““).
EOMONTH function returns the last date for a given date.
Syntax:
=EOMONTH(start_date, months)
Argument months is a serial number, n months represents n months before or after the start date. For example, if A1 is 05/20/2021, we enter =EOMONTH(A2, 1), it returns 06/30/2021 (set cell in proper date format). If we enter =EOMONTH(A2, -1) (negative value in months), it returns 04/30/2021. If we enter =EOMONTH(A2, 0), it returns 05/31/2021.
Named range “Amounts” is the average range in this case.
In the formula bar, select “Amounts”, press F9, values in this range are expanded in an array.
AVERAGEIFS – criteria range1/criteria range2
Named range “Dates” is the criteria range. We have only one criteria range in this case.
In the formula bar, select “Dates”, press F9, numbers which represent corresponding dates in this range are expanded in an array.
AVERAGEIFS – criteria range1
The first criteria is “>=”&E2. Date 5/20/2021 is recorded in E2, in logical expression “>=”&E2, E2 is converted to a five digits number which represents the date 5/20/2021. To concentrate operator “>=” and E2, we use “&” to connect them.
In the formula bar, select “>=”&E2, press F9, number 44336 is displayed instead of the date in E2.
AVERAGEIFS – criteria range2
The second criteria is “<=”&EOMONTH(E2,0). EOMONTH function returns the last day of one month which is decided by the entered date. For E2 date is 5/20/2021, the second argument months is 0, so EOMONTH() returns the last day of May, it is 05/31/2021.
In the formula bar, select “<=”&EOMONTH(E2,0), press F9, number 44336 is displayed instead of the date 05/31/2021.
After explaining each argument in the formula, now we will show you how the formula works with these arguments.
After expanding values in each range reference, in the formula bar, the formula is displayed as:
Now for the criteria range, we keep the numbers which can meet our two conditions “>=44336” and “<=44347”. So only numbers marked in bold meet our conditions.
For the qualified numbers, we mark “True” in the array, otherwise, “False” will be recorded. Then the original criteria range only contains “True” and “False”.
Some numbers in average range are excluded in next calculation step if number’s corresponding value in reference range is 0. So only below numbers are kept and participate in next calculation step “calculate average”.
{5000;4000;0;0;6000;5500;0;0;8500;9000;0;0}
Calculate average of these number:
(5000+4000+6000+5500+8500+9000)/6=6333.33
5. Related Functions
Excel AVERAGEIFS function The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
If you have a lists of dates like MM/DD/YYYY or other formats you like in Microsoft Excel, and you may want to filter them by month ignoring the year, you can have below two ways to do filter. Select one you like or depends the amount of date list, let’s get started.
First we will prepare a list of dates in Excel, see example below:
1. Select Month Directly in Filter Selection
#1 Select Date column and click on Data->Filter. You may notice that arrow is shown in Date.
#2 Click arrow to load filter selection. Just check on both 2019 August and 2020 August in selection field (keep other parameters by default values), then dates in August are auto filtered and displayed in Date column, other dates are invisible.
This is a simple way to filter date by month with ignoring year. And this method is only useful for those dates & months belong to a small number of years. If a large number of years exists, you can try below way to filter date by month.
2. Select by ‘All Dates in the Period’ in Filter Selection
#1 The same way to add filter arrow for Date column. Then click Arrow. In Filter selection, click on Choose One dropdown list.
#2 Drag mouse to the bottom, select All Dates in the Period.
#3 Then the first filter criteria All Dates in Period is selected. Now we click on ‘Choose one’ next to ‘All Dates in the Period’ to select the second filter criteria.
#4 In this list, select the month you want. In this example, we select October.
#5 The all dates in October are loaded even the Year is different.
This way is quite convenient if a large amount of dates with different years in the list. So based on the dates list, you can select the way you like.
3. Video: filter dates by month in Excel with year ignored
In this video, you will learn how to filter dates by month in Excel with year ignored, using two methods: select month directly from the filter drop-down list, or select by ‘All Dates in the Period’ option.
This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel.
Assuming that you have a list of data in range A1:B5, you can use the VLOOKUP function to lookup a value in the first column and then return the corresponding cell value in the same row. But now I do not want to get the cell value, and I want to get the row number from a VLookup in Excel. How to accomplish it.
1. Get Row Number From a Vlookup
If you want to return a row number from a Vlookup, you can use a formula based on the MATCH function. For example, you want to lookup a string value “excel” in range A1:A5 and return the row number where the searching value is found. Like this:
You need to type this formula into a desired cell and press Enter key to apply this formula. You would see that the row number returned.
Note: the above formula only returns a relative row number, and if you want to get a absolute row number, you can use another formula based on the ROW function and the MATCH function, like this:
Type this formula into a blank cell and press Enter key to apply it.
2. Video: Get Row Number from a VLOOKUP in Excel
This video will show you how to use the MATCH function to get a relative row number and how to combine it with the ROW function to get an absolute row number from a VLOOKUP in Excel.
3. Related Functions
Excel VLOOKUP function The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
Excel ROW function The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
This post will guide you how to use Google Sheets DEGREES function with syntax and examples.
1. Google Sheets DEGREES Function Description
The Google Sheets DEGREES function converts radians into degrees. And it will return a numeric value. For examples, if you pass the PI() function into the DEGREES function as its argument, and it will return 180.
The purpose of this function is used to convert radians to degrees in google sheet and it’s returned value is the degrees.
The DEGREES function is a build-in function in Google Sheets and it is categorized as a MATH function.
2. Google Sheets DEGREES Function Syntax
The syntax of the DEGREES function is as below:
=DEGREES (angle)
Where the DEGREES function argument is:
angle -This is a required argument. The angle in radians that you want to convert to degrees.
3. Google Sheets DEGREES Function Examples
The below examples will show you how to use google sheets DEGREES Function to convert radians into degrees.
1# get the degrees of pi radians, enter the following formula in Cell B1.
=DEGREES (PI())
2# get the degrees of 1.25 radians, enter the following formula in Cell B2.
This post will guide you through the process of sorting data in Excel when there is no column header row present.
While having a column header row can make it easier to sort data, it’s not always available, especially if the data was obtained from an external source or created without a header row. Sorting data without a header row can be a bit challenging, but with the right approach, it can be accomplished efficiently.
This post will guide you how to use Excel’s built-in tools and functions to sort a worksheet without column header row in Excel 2013/2016/2019/365. How do I sort data without the first row in Microsoft Excel Spreadsheet.
Whether you’re a beginner or an experienced user, these steps will help you sort your data efficiently and accurately. So let’s get started!
1. Sort Data Without Column Header Row
Assuming that you have a list of data without column header row, and you want to sort all of those values in the given range (A1:C4), you can use Sort feature to sort the data in your current worksheet. Just do the following steps: Step1: select the range of data or the whole column that you want to sort.
Step2: go to DATA tab in Excel ribbon, and click Sort command under Sort & Filter group. And the sort dialog will open.
Step3: you can set the sort criteria in the Sort dialog box, such as: select column A value from the sort by drop down list box, and set Values option in Sort on list box, and set Smallest to Largest option Order dropdown list box, then click Ok button to apply those settings.
Step4: you should see that all data will be sort based on Column A from smallest to largest.
2. Video: Sort Data Without Column Header Row
This video will demonstrate an easy and effective method for sorting data without a column header row in Excel.
This post will guide you how to replace #VALUE! Or #DIV/0! Errors with zero or any specific value in Excel. How do I replace all Formula Errors in your worksheet with Go to Special feature in Excel.
1. Replace # Formula Errors with Zero
Assuming that you have a list of data in range A1:B4, which contain some Errors, such as: #DIV/0!, #VALUE! Or other Errors. And you need to replace all Error message with zero or any other specific value in your selected range in Excel. How to do it. You can use the Go To Special feature to select all cells that contain Error value. Then you can type zero in formula bar, and press Ctrl + Enter keys to apply the same formula to replace errors with zero value. Just do the following steps:
Step1: select the range of cells that you need to replace formula errors.
Step2: go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the drop down menu list. And the Go To Special dialog will open.
Step3: select Formulas radio button in the Select section, and only check Errors checkbox. And then click Ok button.
Step4: all of the formula errors will be selected in the selected range of cells.
Step5: type zero (0) value or any other specific value that you want to replace the errors in selected range. And then press Ctrl + Enter keys to replace all Errors with zero value.
2. Video: Replace # Formula Errors with Zero
This video will demonstrate how to replace # errors with zero or any specific value in Excel using simple and easy-to-follow steps.
This post will guide you how to merge two list without duplicates in Excel. How do I combine two columns of data with remove duplicates command or VBA Code in Excel.
1. Merge Tow List of Data with Remove Duplicates Command
Assuming that you have two list of data, and you want to merge them in the third list without duplicates in your worksheet. How to do it. You can use the Remove Duplicates command to remove duplicate values firstly, then copy one list into another list. Let’s see the following introduction.
Step1: select the first list of data, and press Ctrl + C keys on your keyboard.
Step2: select one cell on the bottom of the anther list of data, and press Ctrl + V to paste it. And keep to select the entire list of data.
Step3: go to DATA tab, click Remove Duplicates command under Data Tools group. And the Remove Duplicates Warning dialog will appear.
Step4: select the Continue with the current selection option in the Remove Duplicates Warning dialog box. And click RemoveDuplicates button. And the RemoveDuplicates dialog will open.
Step5: click Ok button. You should see that all duplicate values found and removed.
2. Merge Two List without Duplicates with VBA Code in Excel
You can use VBA Code to merge two lists without duplicates in Excel. The code can prompt the user to select the ranges containing the two lists and the destination cell for the merged list using the Application.InputBox method. Just do the following steps:
Step1: Press ALT + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, go to Insert > Module to create a new module.
Step3: Copy and paste the code into the module. Save the module and close the Visual Basic Editor.
Sub MergeTwoLists_ExcelHow()
Dim list1 As Range, list2 As Range, mergedList As Range, destCell As Range
Dim values1 As Variant, values2 As Variant, mergedValues As Variant
Dim i As Long, j As Long, n As Long, k As Long
Dim isDuplicate As Boolean
'Prompt the user to select the range for the first list
Set list1 = Application.InputBox("Select the range for the first list:", Type:=8)
'Prompt the user to select the range for the second list
Set list2 = Application.InputBox("Select the range for the second list:", Type:=8)
'Prompt the user to select the destination cell for the merged list
Set destCell = Application.InputBox("Select the destination cell for the merged list:", Type:=8)
'Get the values from the first list
values1 = list1.Value
'Get the values from the second list
values2 = list2.Value
'Combine the two lists into one array
ReDim mergedValues(1 To UBound(values1) + UBound(values2), 1 To 1)
For i = 1 To UBound(values1)
mergedValues(i, 1) = values1(i, 1)
Next i
n = UBound(values1) + 1
For i = 1 To UBound(values2)
isDuplicate = False
For j = 1 To UBound(values1)
If values2(i, 1) = values1(j, 1) Then
isDuplicate = True
Exit For
End If
Next j
If Not isDuplicate Then
mergedValues(n, 1) = values2(i, 1)
n = n + 1
End If
Next i
'Remove duplicates from the merged list
ReDim tempArray(1 To UBound(mergedValues), 1 To 1)
k = 1
For i = 1 To UBound(mergedValues)
isDuplicate = False
For j = i + 1 To UBound(mergedValues)
If mergedValues(i, 1) = mergedValues(j, 1) Then
isDuplicate = True
Exit For
End If
Next j
If Not isDuplicate Then
tempArray(k, 1) = mergedValues(i, 1)
k = k + 1
End If
Next i
ReDim mergedValues(1 To k - 1, 1 To 1)
For i = 1 To k - 1
mergedValues(i, 1) = tempArray(i, 1)
Next i
'Set the range for the merged list
Set mergedList = destCell.Resize(UBound(mergedValues), 1)
'Copy the merged values to the merged list range
mergedList.Value = mergedValues
End Sub
Step4: Press ALT + F8 to open the Macros dialog. Select the MergeTwoLists_ExcelHow macro and click Run.
Step5: select the range for the first list, then the range for the second list, and finally the cell where you want to place the merged list.
Step6: The merged list should now appear in the selected cell.
3. Video: Merge Two List without Duplicates with VBA Code in Excel
In this video, I will show you how to merge two lists without duplicates in Excel using the remove duplicates command and VBA code.
This post will guide you how to use Google Sheets MIDB function with syntax and examples.
1. Google Sheets MIDB Function Description
The Google Sheets MIDB function returns a substring (a specified number of bytes) starting from the middle of a text string and up to a specified number of bytes.
The MIDB function can be used to extract a given bytes of characters from the middle of a text string in google sheets. It returns a substring from a text string at the position that you specify.
The MIDB function is a build-in function in Google Sheets and it is categorized as a Text Function.
2. Google Sheets MIDB Function Syntax
The syntax of the MIDB function is as below:
= MIDB (text, start_num, length_bytes)
Where the MIDB function arguments are:
text-This is a required argument. The text string that you want to extract substring from.
start_num-This is a required argument. The position of the first character that you want to extract in text string. The index from the left of text string from which to begin extracting. And the first character in text string is the index 1.
length_bytes-This is a required argument. The number of the bytes that you want to extract from a text string.
Note:
If start_numis greater than the length of text, the MIDB function will return empty text.
If start_numis less than 1, the MIDB function will return the #VALUE! Error value.
If Length_bytesis negative, MIDB will return the #VALUE! Error value.
If you want to extract a substring from start_num to the end of text string, and you can use the LEN function to calculate the length of the text string, you should not specify a large number to length_bytes argument for this case.
If you want to extract a substring beginning with a particular character, and you should use the SEARCH function to get the index of that character in text string.
Length_bytes must be greater than or equal to 0.
Start_num must be greater than or equal to 1.
If text string has only single bytes characters and the MIDB function returns the same value as MID function.
3. Google Sheets MIDB Function Examples
The below examples will show you how to use google sheets MIDB Text function to extract a substring from a text string.
#1 To extract 10 bytes of substring from the text string in B1, starting at the 5th character, just using the following formula:
=MIDB(B1,5,10) //it returns “le sheets”
#2 If num_chars value is greater than the length of remaining bytes, and the MID function will return all remining characters:
=MIDB("google",3,200) //it returns “ogle”
#3 if text value is a number, and the returned value is also a text by MID function:
=MIDB(1234,2,2) //it returns “23”
Note:
You can use the LEFTB function to extract substring from the left side of a text string in Google Sheets. And you can also use another function called RIGHTB function to extract a substring starting from the right side of the text string. And if you wish to extract a substring starting from the middle position of text string, you can use the MIDB function in google sheets.
4. Related Functions
Google Sheets LEFT Function The Google Sheets LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
Google Sheets LEN Function The Google Sheets LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts exist with number in cell for example Student001-1-A, we drag down the fill handle, the other cells are only copied the original value, number 001 will not be increased automatically. So, we need to find a simple way that to make number with texts continuous in cells. This article will provide you a simple solution to create increment number with text in cells easily.
Precondition:
See screenshot below. For student, we use 001,002,003…to identify them, we can type text like ‘Student001-1-A’ one by one manually, but if there are 100 or 1000 students, it will spend a long time to finish this task. So we need simple ways to solve this issue.
1. Create Increment Number with Texts by ‘&’ in Excel
As we can see the texts are combined as ‘Student00x-1-A’, so we can separate them firstly, then create increment number in one column, and at last combine all texts again. Please see below steps for details.
Step1: Separate texts in column A to two parts.
(Make sure -1-A is saved in Text format, otherwise error will be displayed)
Step2: Select the range covers column C and column D, drag fill handle down to create increment numbers in column C, and text in column D is copied and pasted.
Step3: In cell E2 enter the formula:
=C2&D2
Step4: Drag down the fill handle in column E till reaching the last data.
Step5: Just copy column E, in column A, right click select Paste Special->Paste Values (the first choice).
Then increment numbers within texts are created properly.
2. Create Increment Number Within Texts by Formula in Excel
We can see that in texts ‘Student001-1-A’, except ‘001’ part, other parts are unchanged. So we can extract this part, and then create increment number based on it, and then use formula to combine all parts together.
Step1: Separate texts ‘Student001-1-A’ to three parts. In column C save ‘Student’, column D save ‘1’, column E save ‘-1-A’.
Step2: On D2, drag fill handle down to create increment numbers.
Step5: Drag the fill handle down to fill cells till the last data.
Then you can refer to above method step#5 to copy and paste data properly.
3. Video: Create Increment Number with Texts in Excel
This video will demonstrate how to create an increment number with texts in Excel using the concatenate operator and TEXT formula.
4. Related Functions
Excel Text function The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are easy to learn, so you can select one you like to convert date and time format to Year only.
1. Convert Date & Time to Date by Formula
Prepare a table with date and time, the format is m/d/yyyy h:mm.
If we want to convert them to Year only, we can follow below steps:
Step2: Click Enter to get the result. Verify that format is changed to Year only properly, other part is cleared.
If there are other date and time listed under A2, user can drag the Fill Handle down to fill other cells.
2. Convert Date & Time to Date by Format Settings
You can use the “Format Cells” option to display only the year in a separate cell. Just do the following steps:
Step1: On A2, right click to load menu, select Format Cells.
Step2: In Format Cells window, under Number tab, in Category list, select Customer; then in Type textbox enter yyyy which is the Year format. Then proper Year like 2010 is displayed in Sample field.
Step3: Click OK. Verify that only year is show in cell.
This will display the year in a separate cell, formatted as a four-digit number.
3. Video: Extract Year from Date & Time Format in Excel
This video will demonstrate how to extract the year from date and time format in Excel using a formula and the format cell option.
4. Related Functions
Excel YEAR function
The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
Sometimes we list all products/students or other objects information in one workbook, but list part of them into another workbook based on some criteria. For example, we list all student’s information into workbook Book1 Sheet-All, and list the students who pass the exam in Book2 Sheet-Pass. If we want to lookup students in Book1 refer to the list in Book2, we can apply conditional formatting function to mark these students with proper format.
Precondition:
Table1 in Book1 Sheet-All.
Table2 in Book2 Sheet-Pass.
Now, we want to find out matched values in table1 based on values in table2.
1. Apply Conditional Formatting across Workbook
Step1: In this step, you can copy table2 from Book2 to Book1 Sheet-All just next to table1. Or you can type the formula ='[Book2.xlsx]Sheet-Pass’!A2 into any cell next to table1 to get values from Book2 table2. Drag fill handle down till 0 displays.
Now all names exist in table2 from another workbook/worksheet are copied into current workbook/worksheet.
Step2: Select table1, this table is the range we want to apply conditional formatting function. Click Home in ribbon, then click Conditional Formatting in Styles group, select New Rule in menu.
Step3: On New Formatting Rule dialog, select the last rule type: Use a formula to determine which cells to format.
Step4: In Edit the Rule Description section, in Format values where this formula is true textbox, enter this formula =COUNTIF($E$2:$E$4,$B2)>0. In this formula, $E$2:$E$4 is the range you refer to from table2 (just copied to current worksheet), $B2 is the first name you want to look up from another workbook.
Step5: Click Format button in Preview section. Verify that Format Cells window is displayed.
Step6: Click Fill tab, then select a color as background color. For example, select yellow, then click OK.
Step7: After above step, we go back to New Formatting Rule window. You can see that in Preview section, cell is filled with yellow. Click OK.
Step8: After above all steps, check table1. Verify that matched values are marked with yellow properly.
2. Video: Apply Conditional Formatting across Workbook in Excel
This video will show you how to apply conditional formatting across workbook in Excel using rules that determine the format of cells based on their values.
