Excel

A Guide to Using the MAX and MIN Functions in Excel

January 26, 2023 by Admin Leave a Comment

If you’re working with large sets of data in Excel, you know how important it is to be able to quickly and easily extract the information you need. One of the most useful tools for this is the MAX and MIN functions. In this blog post, we’ll discuss how to use the MAX and MIN functions in Excel.

The MAX function in Excel returns the largest value in a set of values, while the MIN function returns the smallest value. Both functions can be used on a range of cells, a single column or row, or even an array of values. For example, if you have a column of student marks and you want to know the highest mark, you can use the MAX function to quickly find it. Simply select the cell where you want the answer to appear, type “=MAX(“, and then select the range of cells containing your marks. Press Enter and you’ll have your answer.

Student marks Sample data - Maximum and Minimum

In the above sample data to find the max marks all you need to do it to enter =MAX(B3:B7) where B3:B7 represents the datasets. Similarly you can find the minimum of marks using =MIN(B3:B7)

Another quick way to find out the MAX and MIN for a given dataset is to select the dataset and using the menu options available at the top right hand corner under Home Ribbon

In conclusion, the MAX and MIN functions in Excel are incredibly useful tools for working with large sets of data. These functions allow you to quickly and easily extract the information you need, whether you’re looking for the highest or lowest value, or even the most recent or oldest date. With a little practice, you’ll be able to use these functions like a pro and maximize your data analysis skills.

How to display fractions in Excel

December 17, 2022 by Admin Leave a Comment

In excel when we type fractions like ‘1/2’, ‘1/3’, it shows dates like 01-Feb, 01-Mar. To display fractions and stop converting in to dates you can follow the below mentioned steps.

Select the cells or columns where you want the fractions to appear.

Right click and select Format Cells option

Under the Number tab select Fraction.
Select the type of fraction you want. For this example we are going with ‘Up to one digit’. Click Ok to save the changes.

You might be interested in this post as well – Disable conversion numbers with dash to dates in Excel

How to Separate a Person’s First and Last Name in Excel

December 11, 2022 by Admin Leave a Comment

Here are the formulas for separating first name and last name from a person’s full name. Let us take the following sample data of NBA players

Separate First name and Last Name

Let us first start separating First Name from the Player’s name. Type the this formula in C6 Cell.

=LEFT(B6,FIND(” “,B6))

The above formula first finds the single space then Left trims character starting from first character until the start of the single space. Now Autofill this formula for the rest of C6 columns to display the first name.

To get the last name of LeBron James (Cell B6) you can use this formula

=RIGHT(B6,LEN(B6)-FIND(” “,B6) )

What the above formula does is to first get the character count of full name then find starting position of ” “. Then uses RIGHT function to get the characters starting from ” ” till the end of the name. Now drag the formula entered for D6 to rest of the D column.

How to Verify Email Addresses in Google Sheets: A Step-by-Step Guide

December 10, 2022 by Admin Leave a Comment

In Google Spreadsheet you can check whether the value entered is a proper email address by creating a data validation rule. This rule does a simple verification and checks for the @ symbol in the entered value. Let us say you have a column in your spreadsheet that needs to accept only email addresses, then you can do the following to add the validation.

Select the Column values, click the Data menu and select Validation from the menu list.

In the Data Validation window, select Text, Contains for the Criteria drop down and specify the value as @

And if you want to display a message to the user then type the message in Help text some thing like “Enter proper email address example aaaa@xxx.xxx”. Click on the Save button to save the changes.

Now when you enter a value in the email address column without the @ symbol then it would display the message as shown below.

How to increase number of worksheets Excel

March 18, 2016 by Admin 1 Comment

Microsoft Excel by default contains 3 worksheets while creating a new Workbook. For creating a new workbook you can specify the number of Worksheet that a Workbook needs to contain. This can be modified using Excel Options. Listed below are steps for increasing number of Worksheets for a new Workbook in Excel 2013, Excel 2010 and Excel 2007

In Excel 2013 and Excel 2010 –> Click on File menu and then Options.

In Excel 2007 –> Click on Excel Options and then Popular menu.

Under When creating new workbooks section there is include this many sheets fields. This can be used for increasing the number worksheets while creating a new workbook. The minimum value is 1 and the maximum is 255. Check out the below video demo to increase worksheets size in Excel 2013

Also See: How to move or copy worksheets in Excel 2013

A trick to flip a column upside down in Excel

February 17, 2016 by Admin Leave a Comment

Excel users can sort a column using the Sort feature available as part of Sort & Filter menu. But what if you want to flip a column upside down. Let us see this with an example column having some text values as shown below.

We will not be able to apply sort feature directly on this column to flip the data instead we can add a dummy column and use that for sorting the text values.

Step 1: Insert a new column before the existing column.
Step 2: Now use Auto Fill feature to fill the temp with numbers as shown below

Step 3: Now you can use the Sort feature on column1 and make sure to sort by descending order. Also include the column2 in Sort sleection list.

Step 4: Once the columns are sorted, delete the temp column with numbers.