Author

nidhi

Browsing

Want to do some formatting, deleting, or editing options to your data that contains constants? Don’t know how to do these formatting? Then this article will help you to do these options using Go To Constants available in Go To Special Function. Let’s discuss it now.

Go To Constants

The simplest way to do some formatting to your data that contains constants is by using the “Go To Special” function. The “Go To Special” function allows to select cells based on criteria such as cell with constants, formulas, blank one.

1. Select any cell within the table and press Ctrl + A.

go to constants

Pro Tip. Select the cell within the table because if you select Ctrl + A outside the table, it will select the entire worksheet.

2. To choose the constants, go to Home > Find & Select > Go To Special. It will open a Go To Special dialog box.

go to constants

Pro Tip. You can also press F5 or Ctrl + G to open the Go To dialog box. Click on the special option to open the Go To Special dialog box.

3. In the Go To Special dialog box, select Constants and click OK.

As we want only the cells with constants that are text, select text and deselect all the other options.

go to constants

4. Once you click OK, the cells with constants that are text will be selected.

go to constants

Now, To select the cells with constants that are numbers, follow the same procedure but select numbers and deselect all other options and you can probably get this output.

Go To Constants using Go To Special 1

Note. The “Go To Constants” function does not highlight the cells with formulas.

Formatting and deleting:

Now, if you want to know the dates of the sales made during the particular month, you can click on the conditional formatting in the ribbon bar and click on the options available as per your requirements. The steps are:

1. Select any cell within the table and press Ctrl + A.

go to constants

Pro Tip. Select the cell within the table because if you select Ctrl + A outside the table, it will select the entire worksheet.

2. To do conditional formatting, go to Home > Conditional Formatting > Highlight Cells Rules > A date occurring. It will open a date occurring dialog box.

go to constants

3. In a date occurring dialog box, Choose the option for data that you want to highlight according to your preference and click OK.

Here I have selected “This Month” and “Light Red Fill with Dark Red Text” in the dialog box to know the dates of sales made during the current(August) month.

go to constants

4. Once you click OK, the changes will be made in the cells that contains date.

The below image will help you to comprehend better.

go to constants

Now, to only know the details of the product that have been sold, follow the procedure as below in the same worksheet.

1. Follow the same steps to open the “Go To Special” dialog box.

2. Once you have opened the dialog box, Choose Constants and select the Numbers option.

go to constants

3. Once you click OK, only the numbers columns will be highlighted.

go to constants

4. Now, Click delete on your keyboard and check your worksheet.

You can see my worksheet having no numbers and only the details of the products left.

go to constants

What happens when you select everything under “Go To Constants”?

Now, let’s see what the kind of output we get on selecting all the options under constants in the Go To Special dialog box.

1.Follow the same steps to open the “Go To Special” dialog box.

2. Select all the options and click OK.

go to constants

3. Once you click OK, everything will be displayed.

go to constants

Pro Tip. The technique of identifying some special cells have some applications. Errors and labels can be used to find the errors made in the cells containing formulas in your worksheet.

Hope you enjoyed reading Go To Constants. To know more about the shortcuts regarding Go to Special, Visit Here.

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Looking for a piece of specific information but unable to search? Then let me introduce you to the Search Function in Excel. The Search function will enable to get to your information instantly.

Let’s begin our hunting journey with excel functions.

A quick view on the SEARCH Function in Excel

SEARCH function in Excel is a built-in function that returns the location (position) of a substring in a string. So, in simple words “SEARCH” function helps you to find the position of the letter that you are searching within a word.

SYNTAX:

=SEARCH ( find_text, within_text, [ start_num] )

How to use SEARCH function in Excel

1. SEARCH FUNCTION without specifying the position

To perform the SEARCH function in Excel, follow the steps given below.

Step 1: You need to enter the texts.

Step 2: Now, enter the syntax to perform the search function.

Using Search Function in Excel

Step 3: Press Enter Key.

How to use SEARCH Function in Excel 2

Here, the SEARCH function has returned the value “3” because I have searched for “c” in the cell “B6”. This means “c” is in the 3rd position within the searched string.

2. SEARCH function with specifying the position

In this next example, I have mentioned the position of the character from where the search has to begin. I have specified the position as 3 from where my search has to begin for the letter “n” within the cell “B6”.

Example 1

SYNTAX:

= SEARCH (“n”, B6, 3)

How to use SEARCH Function in Excel 3

Note:

It’s not a big deal even when you don’t specify the position because it will return you the same search result and that doesn’t makes a great difference.

Example 2

If you search for letter “l” from the 15th position, it will show you an error because there is no 15th position in this word.

SYNTAX:

= SEARCH(“I”,B6, 15)

How to use SEARCH Function in Excel 4

Here, I have searched for the substring “I” within the string “excel in excel” and I have commanded excel to perform the search result from 9th position. Since excel couldn’t find “I” when search is performed from 9th position, it has returned me an value error.

Example 3 (zero & negative numbers)

When you specify zero or negative numbers for the position from where the search has to be performed, then SEARCH function returns you an error.

SYNATX:

= SEARCH (“e”, B6, -5)

How to use SEARCH Function in Excel 5

3. SEARCH function with and without specifying position (repetition of words)

Example 1 (without specifying position)

SYNTAX: = SEARCH (“e”, B6)

How to use SEARCH Function in Excel 6

The syntax clearly depicts you that I have searched for the substring “e” within the string “excel in excel”.  SEARCH function has returned me the value “1” denoting the position of “e’ found in the first position. If you don’t mention the position, SEARCH function begins searching from the first and returns you the position of the searched letter as soon as it encounters it.

Example 2 (with specifying position)

SYNTAX: = SEARCH(“e”, B6, 7)

How to use SEARCH Function in Excel 7

Here, I have searched for the same letter “e” as depicted in the previous example. But, the only difference here is that I have specified the position from where the search has to be done. Since, I have specified the search to begin from 7th position, it ignores the “e” in first and fourth position and returns the value “10”.

4. A clear depiction of the differences between “FIND” and “SEARCH” function

The following two examples clearly depicts the difference between “FIND” and “SEARCH” function in Excel.

Example 1

SYNTAX:

=SEARCH (“e”, B6) for result 1.

= FIND (“e”, B7) for result 4.

How to use SEARCH Function in Excel 8

Here, you can clearly see that the SEARCH function has returned “1” for my search “e” and “FIND” function returned “4” for the same search “e”.

How to use SEARCH Function in Excel 9

This is because the “FIND” function is case-sensitive and ignores the “E” in the first position and takes into account the next “e”. Since the SEARCH function is case-insensitive, it has returned the position of “E” which is in the first position itself.

Example 2

Unlike FIND, SEARCH function in Excel also permits the usage of wildcard characters in the find_text argument.

SYNTAX:

= SEARCH (“are***excel”, B6)

How to use SEARCH Function in Excel 10

Here, I have searched for the word “are” and the SEARCH function has returned me the position of the beginning letter of the word “are” that is 4 (position of “a”).

 Exceptional cases

SEARCH function in Excel returns you #VALUE! if the substring that needs to be searched is not found within the body of the string.

SYNTAX:

=SEARCH (“a”, B5)

How to use SEARCH Function in Excel 11

Here, I have searched for “a” in the cell B6. Since, the word “excel in excel” doesn’t contain the letter “a” that is why SEARCH function has returned #VALUE! Error.

Key differences between “FIND” and “SEARCH” function

SEARCH function differs from FIND function in two ways

  1. SEARCH function in Excel is case-insensitive.
  2. It allows using wildcard characters.

For more information visit our YouTube channel.

Are your exported data dates are not in regional dates? Are you finding it difficult to replace this feature? Did you know the data type of your date can be changed? Then this article will help you to convert text to date format using Text to Columns option. Let’s get started.

Set Regional Dates with Text to Columns – An Introduction

Generally, when you download data from online like any financial data, the dates wouldn’t be of the same format. Though they view like dates, the data would actually be a text. To find whether the data is in text or date format, you can make use of a filter drop-down menu or number format drop-down menu.

Example: Here I have downloaded a financial file and all the dates are not in the region and I have to change it to regional dates. Now we will find whether the data is in text or date format using a filter drop-down menu. The steps for using the filter drop-down menu are as follows:

1. Select any cell within the table and go to Home > Sort & Filter > Filter.

regional dates

Pro Tip. You can also make use of the shortcut key to apply the filter. Select any cell and press Ctrl + Shift + L to apply the filter.

2. Once you click Filter, the filter will be applied to the headers in the form of drop-down menu.

regional dates

3. You can click on the drop-down arrow to see the filtering options for the headers.

Once I click the Date (Header) Filter, You can see the Date column is not grouped by month and year but all the dates are formatted in texts.

regional dates

The next option to find whether the data is in text or date format is by using a number format drop-down menu. The steps for using the number format drop-down menu are as follows:

1. Select any cell in the date column within the table and go to Home > number format drop-down menu.

regional dates

2. Once you click on the number format drop-down arrow, it will show you the list of options to format your cells.

Here, you can see that the dates and the numbers are the same in the formatting options. This is because Excel is not able to identify the data as a date.

regional dates

I will just show you an image of the formatting option which considers the cell as a date. The below image will help you to comprehend better.

regional dates

Convert Text into Data Format using Text to Columns Option:

The options like Find and Replace doesn’t really work in formatting dates because of the difference in regions. Alternatively, the Text to Columns feature works no matter which part of the world you are. The steps to convert text into date format are as follows:

1. Select the texts that you want convert into dates.

regional dates

Pro Tip. To do it quickly, Select the data first cell and press the shortcut key Ctrl + Shift + Down Arrow Key (on the keyboard)

2. Then go to Data (Tab in the ribbon) > Text to Columns.

regional dates

3. Once you select Text to columns, a dialog box appears in the window. It consists of 3 steps.

regional dates

4. In first step, Select the delimited option in the dialog box and click Next.

regional dates

5. In step 2, Since the Tab option is already selected, don’t make any changes and click Next.

regional dates

6. In step 3, Select the Date option and choose Format where the data at present shows. That is if the text is Day – Month – Year then choose DMY but if it is Month – Date – Year then choose MDY. Then click Finish.

regional dates

7. Once you click Finish, you can see that the text is converted into date format.

In the below image, you can see that the data is changed from text to date format. I have used a filter drop-down menu to show you the difference in the formatting option. Once I click the Date (Header) Filter, You can see the Date column is grouped by month and year because it has considered the column as a date format.

regional dates

Note. Since Text to columns sets the dates per the region automatically, you do not have to concentrate and changing the region/language in the Microsoft settings.

Refer to the linked articles to learn more about filter and number format options.

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Did you know you can get your dates in week number (1 – 54) using the WEEKNUM Function in Excel? Sounds interesting? Yes, you can actually allot your dates in week numbers of the year in Excel. You can enter the dates and get the exact week number for it.

Introduction to WEEKNUM Function

You can find this option as Date/Time Function which is actually a default function in Excel. The WEEKNUM Function returns the number of the week in the entire year.

Syntax : =WEEKNUM (serial_date , [firstdayofweek])

Instance:

Consider the below image, we have mentioned a date for which we wish to calculate the WEEKNUM Function.

WEEKNUM Function

To calculate the WEEKNUM, enter the syntax =WEEKNUM(B5,2). B5 is the serial date for which we wish to calculate the Week Number. 2 represents Monday, as our weekday starts from Monday we chose that. You can choose any day as the starting day of your week as shown in the image below:

WEEKNUM Function

The result will appear as shown in the image below. IT returns 31 ans 01-08-2020 falls in the 31st week of the year.

WEEKNUM Function

Note – The serial date calculates the number of the week and since Sunday is the default for the first day of the week, the week starts with Sunday. Hence the numerical value is 1. Once you enter this syntax, the WEEKNUM gives you the day of the week in numeric value. This function is applicable only to Excel 2007, 2010, 2011, 2013, 2019 and Office 365. However, you have to be sure of the column of the serial date and the day to get the right output.

Visit our Youtube Channel for more updates.

Learn more about WEEKNUM Function at https://excelinexcel.in/ms-excel/functions/excel-weeknum-function/.

Did you know that you can actually schedule your work in adjustment to your holidays in the future? In this post, let’s learn how to use the WORKDAY function to relieve you from work trouble and have a better schedule.

Introduction to WORKDAY Function

The WORKDAY function basically provides back the date N working days of either the past or future and enables you to include or remove your work date. The WORKDAY function also includes the weekends and holidays to give you a better understanding and space for you to schedule your work. Generally, the WORKDAY function is used to get notified about the completion dates, delivery dates, submission dates, ship dates, etc. The way to use the WORKDAY function is to calculate with positive numbers for future days and negative numbers for past days.

If you are using an Excel version before the 2007 version, then you would have to initiate the Analysis toolbar, but in Excel 2007, 2010, 2013 and 2016 the WORKDAY FUNCTION comes by default. To enable this function, the following input is required,

Syntax: WORKDAY (start _date, days, [holidays])

Example on how to use the WORKDAY Function:

Let’s say you are a Project Manager in XYZ company. You wish to find the end date of your project. Do do that consider the following image. In the below image, we have mentioned the starting date, the total number of days required to complete the project. We have also mentioned the holidays. We wish to calculate the Workday i.e., the day when we will reach the completion of the project.

How to use the WORKDAY Function

To calculate the WORKDAY, enter the syntax =WORKDAY() in the cell where you want the calculated workday to appear.

There are three arguments in the syntax. The Start date, Days and Holidays. In the start date mention the starting date of the project. In the Days, mention the number of days for completion of project. And finally select the holidays range from your data.

  • For our example,
  • Select the cell where you want the output to appear.
  • Enter the Syntax. For our example the syntax is =WORKDAY(E5,E6,F6:F10). E5- Starting date, E6- Number of days for completion, F6: F10 – Holidays.
How to use the WORKDAY Function

Once you entered the Syntax, hit ENTER. The WORKDAY Function will return the estimated date of completion as shown in the below image:

How to use the WORKDAY Function

Note – The positive numbers are added in the days in the formula and to reduce days the negative number is added in days in the formula, for example,

  • Another tip on how to use the WORKDAY Function is you can also schedule the present date work using the below syntax:
  • WORKDAY ( TODAY ()  30 )
  • WORKDAY ( TODAY ()  -30 )

To know more, Visit our Youtube Channel.

To know more on how to use the WORKDAY Function, Visit Here.

Why and How to use DAYS Function in Excel? As humans, we all have the tendency to gift our loved once on a special occasion. But what if you have no idea how many days are left to that occasion to purchase a gift? Here is a solution to it. In this post, we will discuss how to use DAYS Function in Excel to count the difference between two dates.

A quick view on “DAYS FUNCTION”

Excel introduced the DAYS function in 2013 for calculating the number of days between two dates. This is a built-in function and categorized under date/time function. DAYS function helps us to calculate the number of days with just feeding them with two arguments. End_date and start_date are the two arguments that we need to feed in to get the output.

Syntax:  =DAYS (end_date, start_date)

Let us understand how we can use the DAYS Function in different scenarios:

Instance 1

Are you curious to plunge into the practical part? If yes, work along with me:

  • Open a new excel worksheet and enter some data (dates) that you wish to find the days left between.
  • Here I have entered the following dates and the syntax which I have used returned me the following results.
    SYNTAX for cell C2: =DAYS(B5,C5)
How to use DAYS Function in Excel

Hope this has given you a clear understanding of How to use DAYS Function in Excel at a basic level.

Instance 2

In the below image you can see that the cells B4 and C4 have returned a negative value.

How to use DAYS Function in Excel

Know why?

It’s because the entered data is not in the proper order. The End Date is prior to the Start Date, this results in a reverse days count. In such scenarios, excel’s DAYS function returns you a negative value.

Note – In circumstances where a negative value is returned, the Absolute (ABS) function can be used to get a positive value. Syntax for Absolute Function is = ABS(DAYS())

Instance 3

Here is another example where your results are denied by Excel. This happens when the data you have entered cannot be accessed by Excel. In many cases, we tend to do a typing error or enter such values which are not recognized by Excel, it returns an error.

In the above given example, by mistake, I have entered a date 31st September in cell B4 which does not exist. So it returned a #VALUE! Error.

How to use DAYS Function in Excel

Note – function “DAYS” expects correct parameter values.

Instance 4

In this final example, here I have entered numbers instead of dates, as usual DAYS function has returned me the number of days between the two numbers that I have entered. In such cases, what happens is excel converts each number into dates and then returns you the answer.

How to use DAYS Function in Excel

Note – To know the conversion of numbers into dates, you can use the shortcut Ctrl + Shift + #

To know more, follow us on our Youtube Channel.

To know more on how to use DAYS function in Excel, read here: find difference between days.

Wish to sum only largest or smallest values? But confused on it’s possibility? Here is an article to help you learn how to sum those values.

How to Sum Large Values only?

Let’s say you wish to sum only the largest numbers from a given range. Use the Sum and the Large function to do so. You can specify up to how many numbers you wish to sum. For example, you wish to sum 2 largest numbers from a given range, then you can specify the criteria as 2.

To understand much better, consider the below given data-set:

In the data-set, we wish to sum the first two largest values in the cell. To do that, follow the given steps:

  • Enter the formula =SUM(LARGE())
  • Specify the range from which you wish to choose the largest numbers. In our example, the range is C5: C10.
  • Select the number of values you want to sum up. Let’s say in our example we wish to sum the first two largest values from the range. So specified as 1,2 as shown in the below image:
Sum only Largest or smallest Values

Once you enter the formula, hit Ctrl + Shift + Enter. The result will appear in the image below. It has summed up the two largest values in the range i.e., 30,000 and 31,000, and produced the output as 61,000.

Sum only Largest or smallest Values

Note – The formula =LARGE(C5:C10, 2) will return the second largest value from the selected range.

How to Sum Small Values only?

Consider the same data-set as above and this time we wish to Sum small values only. To sum only the small values, follow the given steps:

  • Enter the formula =SUM(SMALL())
  • Specify the range from which you wish to choose the smallest numbers. In our example, the range is C5: C10.
  • Select the number of values you want to sum up. Let’s say in our example we wish to sum the first two smallest values from the range. So enter in the flower parenthesis as {1,2}
  • Click Enter.
Sum only Largest or smallest Values

Once clicked enter, the output will appear as below. The output cell C11 is the sum of C8+ C5 i.e, 15,000 + 25,000. The sum of two smallest values in the selected range.

Sum only Largest or smallest Values

To know more on How to Sum, visit here.

For more updates, visit our Youtube channel. Hope you enjoyed learning on Sum only Largest or smallest Values.

Is finding the maximum value from a range is difficult? Do you not know how to calculate the maximum value from a range? Don’t worry, as Excel is here to help you and make your work light.

Let us understand this with an example. Consider the below data-set. It has a list of students with the marks acquired in Math subject. We would like to calculate the maximum marks scored by a student for this particular subject.

How to calculate the Maximum Value from a Range

To calculate the maximum value from a range, follow the given steps:

Step 1 :Type the word MAX in a cell

Step 2 :With the help of your cursor select the range for which you want to find the maximum value. Type down the closing parenthesis

How to calculate the Maximum Value from a Range

Step 3 :And press Enter on your keyboard.

By this, you can find the maximum value from the range as shown in the below example:

How to calculate the Maximum Value from a Range

Hope you enjoyed learning How to calculate the Maximum Value from a Range.

To know more, Visit our Youtube Channel

Learn more on MAX Function Here.

In this article, let us discuss how to use SUM in Excel to sum a range of cells in a column and row, how to sum non-contiguous cells, and much more.

Sum a Range of Cells – Column-wise:

Most of the time a SUM Function is used to sum a set of data in Excel. Let us understand how to use the Sum Function with the below given example. To use the function you need to enter a formula =SUM() in the cell where you want the output to appear. Select the range you want to Sum and click Enter.

In the given example we wish to Sum a range of cells from C5 to C9. To do so. follow the given steps:

  • Enter the formula in the cell C10 as = SUM().
  • Select the range to sum ie. C5: C9.
  • And lastly, click Enter.
How to use SUM in Excel

After following the above steps, the output will appear as below:

How to use SUM in Excel

Sum a Range of Cells – Row-wise:

Most of the time we prefer to Sum a range of cells column-wise. But what if you want to Sum a range of cells in a row? Follow the given steps to learn how to use sum in excel to Sum a row.

Consider the below data-set. We have a set of items and their corresponding sales. We wish to add all the sales values in the sth row of column I.

How to use SUM in Excel
  • Enter the Formula in the cell I5 as =SUM()
  • Select the range you wish to sum i.e., C5:H5 in our example.
  • Click Enter.
How to use SUM in Excel

The output will appear as shown in the below image. We got a Sum total of 1,56,000 in the cell I5.

How to use SUM in Excel

Sum Non-contiguous Cells:

If you wish to add only only cells in a range, then you can do so by following the given steps:

Consider the below data-set. In the given data-set we wish to add only the cells with the amount 30,000 and 31,000 ie.e, the cell C6 and C8. To do that:

  • Enter a formula =SUM in the cell C10.
  • Select the cells you wish to add. For our example we have selected cell C6, C8.
  • Click Enter
How to use SUM in Excel

The output is portrayed in the below image. The output in the cell C10 is a sum total of the cells C6 and C8 i.e., 30,000 + 31,000 = 61,000.

How to use SUM in Excel

How to use Sum in excel to add an Entire row or column?

Due to large data-sets, we may not able to view all cells in a row or column. So to add an entire row or column simple use the formula :
=SUM(A: A) to add an entire column. Enter the column name you wish to add in parenthesis.
=SUM(1:1) to add an entire row. Enter the number of the column you wish to sum up.

To learn to use AutoSum and Auto-Calculate, Visit Here

To Know more on How to Sum a Range of Cells with or without Error, Visit Here.

Hope you enjoyed reading How to use SUM in Excel. For more updates, Visit our Youtube Channel.

Did you know you can compare two workbooks and scroll them at the same time using Synchronous Scrolling? Excel gives you access to also order many worksheets and look onto it at the same time. The options are different in the Excel versions. So are you searching for a way to scroll and compare two workbooks and make your job faster and quicker?

To do Synchronous Scrolling, in Excel 2003:

  • Click on the window icon,
  • Select compare side by side
  • And then select synchronous scrolling.

If you use a Excel 2007 or above versions then follow the given steps:

  • Select the View tab on the ribbon area
  • And select the view side by side option
  • And then select the synchronous scrolling option. This is not only easy but also makes your work much better.
Synchronous Scrolling

To know more, visit our Youtube Channel.

To learn more about Excel, Visit Here.