Digital Answers

Streamline your Excel spreadsheets with these easy steps to hide rows and columns

February 4, 2023 by Admin Leave a Comment

Excel spreadsheets can get cluttered and disorganised with too much data, making it difficult to find what you need. Hiding rows and columns in Excel can be a simple solution to keep your data organised and easily accessible.

Steps to Hide Rows and Columns:

Select the rows or columns you want to hide. To select multiple rows or columns, hold down the “Ctrl” key (Windows) or “Cmd” key (Mac) while clicking on each row or column.
Right-click on the selected rows or columns and select “Hide”.

To unhide the rows or columns, select the rows or columns immediately above or to the left of the hidden ones.
Right-click and select “Unhide”.

Another way to hide rows and columns is to use the “Format” dropdown menu on the Home tab. Simply select the rows or columns you want to hide, then go to Home > Format > Hide & Unhide > Hide Rows or Hide Columns.

Format Menu - Hide or Unhide Rows and Columns in Excel

Benefits of hiding rows and columns:

Keeps your spreadsheet organised and easier to navigate
Helps to focus on relevant data and minimise clutter
Protects sensitive data by making it invisible
Doesn’t delete the data, only makes it invisible, so it can still be used in formulas and calculations

You can also check out the steps for hiding rows and column headers in excel and Search, hide, print comments in Excel

Effortlessly Convert Text Cases in Excel with These Methods

February 1, 2023 by Admin Leave a Comment

In this post, we will explore the different methods you can use to convert text cases in Excel and provide step-by-step instructions to help you get started.

UPPER

The UPPER function is used to convert text to uppercase. The syntax is simple: =UPPER(text). To use the UPPER function, simply select the cell that contains the text you want to convert, then type =UPPER(A2), where A2 is the cell reference of the text you want to convert. The result will be displayed in the formula bar and can be copied and pasted as values to permanently change the text.

LOWER

The LOWER function works similarly to the UPPER function, but converts text to lowercase instead. The syntax is =LOWER(text). To use the LOWER function, select the cell that contains the text you want to convert, then type =LOWER(A2), where A2 is the cell reference of the text you want to convert.

PROPER

The PROPER function is used to convert text to proper case, which means that the first letter of each word is uppercase and the rest of the letters are lowercase. The syntax is =PROPER(text). To use the PROPER function, select the cell that contains the text you want to convert, then type =PROPER(A2), where A2 is the cell reference of the text you want to convert.

Here is the screenshot of sample data after applying all the text cases functions

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.