Faysal Easy Excel

How to SUM by matching partial text in Excel

2024-03-07T13:21:00.015+06:00

How to SUM by matching partial text in Excel

In this tutorial, I'm going to share another tips on how to sum by matching partial text in Excel.

First of all, Microsoft Excel has a function called, "SUMIFS". Also has SUMIF. But I'll work here with "SUMIFS". This function allows us to sum a specific range. Based on some specific conditions. The situation of your condition is different in real life. As a result, I'm writing how to sum by matching partial text in excel tutorial.

How to sum by matching partial text example:

if you want to SUM only the BD based name shown in the list below, what will you do?

Image 1: How to SUM by matching partial text in Excel

Well, in this case You can do by following below steps:

So, as you can see, I have to check all cells and find if the string “BD” appears in the cell or not, and if it does, I need to include the value in the adjacent cell in column B while doing the sum.

The above example is a case of a partial match, where I need to sum cells based on the partial match.

I'm going to use a criteria "*BD". This criteria states that, all words that ends with BD. So, i'm sure below formula (very easy for all). The formula with partial match to find the total sales quantity is:

Here is the formula:

=SUMIFS(B2:B7,A2:A7,"*BD")

The formula details are:

B2:B7 – This is the range, which has the values that we want to sum.
A2:A7 – This is the criteria range, that contains names.
“*BD” – I have used “*BD”. This means, any cell that ends with the string "BD" will satisfy the criteria.

Finally 21 + 14 + 54 in addition the result will show: 89

How to SUM by matching partial text in Excel article is relates to:

excel match function
excel match
excel match two columns
excel match formula
excel match two columns and output third
excel match multiple criteria
excel match index
excel match function two columns
excel match type
excel match and index function
excel match values in two columns
excel match multiple columns
excel match text
excel match columns

How to Create a Checklist in Excel

2023-11-24T04:42:00.010+06:00

How to Create a Checklist in Excel

Checklists in Excel:

First of all I would like to say, Microsoft Excel is a powerful tool. Because, It is not just for numbers, but additionally for organizing your tasks. Due to you have to do many works in corporate office, you may need a tracker. To track your daily official task, you can simply create a Checklists in Excel with our simple and very easy tutorial. Yes you can use it for non-public and professional duties.

So, I created a "To Do List" or "Checklist in Excel" to track my all corporate task. Due to many friends of mine unable to watch my YouTube video tutorial, hence I decided to share it with my blog. You can also watch my YouTube video tutorial and download the free excel templates.

You can customize this checklist in Excel by way of selecting exceptional codecs, colorings, and layouts as you want.

Image 1: ToDo list template tutorial in excel

Step-by-Step Tutorial to Creating an easy Checklist in Excel:

A. Setting Up Your Excel Sheet 1. First of all, Open your Microsoft Excel and Create a new workbook.

2. In Column "A1" type "Task Serial", "B1 Task Details" and "C1 add a checkbox from Developer Tab".

Image 2: Insert a Check box from Developer Tab

3. Apply a simple and easy cell format your worksheet like below image:

Image 3: Checklist in Excel design

4. Select the first check list from C2 cell.

5. Right click on it and select Format Control.

Image 4: Checklist in Excel "Format Control"

6. Click on Control tab, set value as Unchecked. And in Cell link box Click on "D2" cell.

Image 5: Checklist in Excel checkboxes settings

7. By this way, set all the checkboxes to C3, C4 and C5.

8. You are done. Now, when you have done any task, simply Click on the "Checkboxes" besides that task to mark as done.

Conclusion

Creating Excel checklists permits efficient venture management and productivity enhancement. Excel's customizable capabilities cater to diverse needs, and its superior functionalities provide alternatives for streamlined task tracking and management.

Free Download This Template:

https://www.youtube.com/@faysaleasyexcel

Watch it on YouTube:

FAQs

1. Can I add new items to an existing checklist in Excel?

Yes, simply insert a new row and include the task along with its associated checkbox.

2. Is it possible to share Excel checklists with others?

Yes, Excel files can be shared via email or cloud storage platforms for collaborative work.

3. Can I create multiple checklists within the same Excel sheet?

Certainly, by dedicating separate sections or tabs within the workbook.

4. Are there any shortcuts to quickly navigate within a lengthy checklist?

Using Excel's navigation shortcuts like Ctrl+Arrow keys allows swift movement within a checklist.

5. How can I print an Excel checklist?

You can print your checklist by going to File > Print and selecting the appropriate print settings.

I hope this tutorial will help you a lot for beginners. The advance level viewers can watch my YouTube tutorials for more.

Happy excelling.

Infographic Column Chart in Excel 2021 | Cylinder Chart in Exce

2023-02-21T08:01:00.000+06:00

Infographic Column Chart in Excel 2021 | Cylinder Chart in Exce

Please SUBSCRIBE our YouTube channel for more videos.

Infographic Sales Performance Bar Chart Design in Excel 2021 | Excel Chart

2023-02-17T08:54:00.002+06:00

Learn how to create an engaging and informative Infographic Sales Performance Bar Chart in Excel 2021 with this step-by-step tutorial. In this video, we'll guide you through the process of creating a professional-looking bar chart that highlights your sales performance data, complete with customizable elements like color-coding and labels. Plus, we're also sharing a link to download the Microsoft Excel workbook for free, so you can use the chart for your own business or personal use. Follow along and take your data visualization skills to the next level!

Infographic Column Chart in Excel 2016 | Pen Drive Chart in Excel

2022-11-06T18:30:00.009+06:00

New Excel Chart Tutorial just published on YouTube channel. Download your free copy. Please SUBSCRIBE and download more than 100 infographic Microsoft Excel Charts absolutely FREE.

#faysaleasyexcel #exceltips #exceltricks #exceltutorial #msexcel #excelcharts #charts

https://docs.google.com/spreadsheets/d/1xtlT43N9_CSbPw1_Exb5OqD9i1rW5wgG/edit?usp=sharing&ouid=110777291355617494364&rtpof=true&sd=true

Beautiful Cylinder Chart in Excel 2016 | Excel Cylinder Chart

2022-08-30T01:22:00.005+06:00

Infographic Cylinder Chart in Excel 2016 | Beautiful Excel Charts

2022-08-29T18:27:00.004+06:00

Infographic Line Chart in Excel 2016 | Line Chart

2022-07-15T06:22:00.004+06:00

Infographic Test Tube Chart in Excel 2019

2022-03-25T06:21:00.004+06:00

Population Pyramid Chart in Excel 2016

2022-03-07T05:01:00.007+06:00

Maximum and Minimum Value Chart in Excel 2016

2022-02-20T21:33:00.007+06:00

In this video you will learn how to highlight or plot the high or low point in a single column chart. I've used 2D Stacked Column Chart in Excel 2016 in this technique. Hope you will enjoy it.

Loading Chart Decoration in Excel 2016

2022-02-01T21:27:00.004+06:00

Loading Chart is also known as Progress Chart. In this tutorial I've shared how you can decorate a Loading Chart in Excel 2016.

Pyramid Chart Decoration in Excel 2016

2022-01-30T18:38:00.003+06:00

Easy Progress Chart or Doughnut Chart decoration in Excel 2016

2022-01-29T18:27:00.003+06:00

Simple Doughnut Chart Decoration in Excel 2016

2022-01-25T15:14:00.004+06:00

In this video we will show you the Step by Step process involved in creating a Doughnut Chart. Please SUBSCRIBE our YouTube channel for more upcoming videos. #faysaleasyexcel #exceltricks #exceltips #exceltraining #excelskills #doughnuts #excelcharts #doughnuts #microsoftexcel

Please SUBSCRIBE our YouTube Channel for more upcoming videos.

Design your Doughnut Chart in Excel 2016 Quickly

2022-01-23T07:37:00.005+06:00

In this video, you will learn how to design a Doughnut Chart quickly in Excel 2016. This is simple but looks attractive. You can try it to impress your Boss or clients.

Please SUBSCRIBE our YouTube channel for many more charts.

Design your new Infographic Pie Chart in excel 2016

2022-01-21T08:10:00.003+06:00

Design your new excel Pie Chart like a Pro for your Business Review Presentation in 2022.

Join two or more cell values into one Cell in Excel 2016

2021-11-24T07:16:00.004+06:00

In this tutorial you will learn, how to join, combine or merge cell values from two different cells or more than two different cells into one single cell in Excel 2016. There are many ways to combine or merge two cell values into one cell in Excel. In this tutorial I will explain 3 ways that will let you join data from several columns into one without losing data. These are CONCATENATE Function, Custom Formula and Flash Fill.

A. CONCATENATE Function to Combine Two or More Columns into One:

If you have a data table with "First Name" and "Last Name" like below image and you want a new column where "First Name" and "Last Name" will combine row by row with a space into one, then follow the below steps:

1. In C3 cell enter this formula:
=CONCATENATE(A3," ",B3)
2. Press Enter.

CONCATENATE Function joins the cell values. In this example CONCATENATE Function first joins A3 cell value, then a space between a quotation marks (" ") added a space. And finally joins B3 cell values.

Tips:
CONCATENATE Function cannot join more than 255 Cells. If you want to join more than 255 cells, then you can use Ampersand and Quotation Mark (&""&).

B. Combine two or more columns by using Ampersand and Quotation Mark Formula:

As you have just informed that, CONCATENATE Function unable to join more than 255 cell values, you can use Ampersand and Quotation formula. Not only for joining more than 255 cell values, you can use it always. To know how to combine two columns by using Ampersand and Quotation Mark formula on the same above data table, follow the below steps:

1. In C3 cell type this formula:
=A3&" "&B3
2. Press Enter.

This will join the cell values of "First Name" and "Last Name" with a space in C3 cell. You can use this formula (&""&) more than 255 times.

C. Combine two or more columns by using Flash Fill:

You can combine two or more columns by using Flash Fill feature in Excel 2016. This is very simple to use. To know how, follow the below steps:

1. In C3 cell first type a "First Name", for example: Zakir
2. Press a Spacebar.
3. Then type a "Last Name" of the same row. for example: Hossain
4. Press Enter. So C3 cell contains "Zakir Hossain".
5. Now Select C3:C11.
6. Click on Data | Flash Fill

Tips:

If you want to get the result quickly, then use "CTRL + E" or "ALT + A + F + F" Keyboard Shortcut Key.

Video Tutorial from our YouTube Channel:

Remove Duplicate values but keep one value in excel 2016

2021-11-11T06:21:00.005+06:00

Remove duplicate values but keep one value:

So, In Microsoft Excel when you start data analysis based on one column, it is very important to keep your data clean and must follow the data table rules. Otherwise your query on data table will show you wrong result. One of the data table rule is, every record or row will be unique based on one column. In Microsoft Excel you can check and remove duplicate values or rows based on one column. This feature in Microsoft Excel is called Remove Duplicate. It is a built-in tool of Microsoft Excel which helps you to clean up your data quickly. It is very easy to use.

In this tutorial I'll show you how you can remove duplicates rows in excel and keep first record or row only from each duplicate records or rows based on one column.

How to use Remove Duplicate in Excel 2016

Let's start with a simple example here. Below is a data table where few columns contains duplicate values. We can remove these duplicate values but keep the first value based on one column or based on multiple columns.

Use Remove Duplicate for Single Column:

There are duplicate values in Name column. So, if we want to remove duplicate names and keep the first record only, then follow below steps:

1. Click anywhere of the data table.
2. Click on Data | Remove Duplicates.
3. Remove Duplicates dialog box will appear.
4. Click on Unselect All.
5. Check on Name

6. Click Ok.

Tips:
Make sure that, "My data has headers" option in "Remove Duplicates" dialog box checked. Otherwise you have to identify with Column Number like Column 1, Column 2 etc.

Microsoft Excel will notify you with a message that, how many duplicate values found and removed, also how many unique values remain in Name column.

Use Remove Duplicate for Multiple Columns:

If there is a criteria that, if Name and Salary is same value for a single record, then it is must be a duplicate. So you have to keep first and delete others same record. To do this, follow the instructions:

1. Click anywhere of the data table.
2. Click on Data | Remove Duplicates.
3. Remove Duplicates dialogbox will appear.
4. Click on Unselect All.
5. Check on Name and Salary

6. Click Ok.

Microsoft Excel will notify you with a message that, how many duplicate values found and removed, also how many unique values remain in Name column.

How many ways you can identify duplicate values in Excel 2016:

There are 2 ways you can identify the duplicate records. These are:

A) Through Conditional Formatting:

To identify duplicate records through conditional formatting, follow below steps:

1. Select the Data Table (do not select the Header Row)
2. Click on Conditional Formatting | New Rules

3. Select Use a formula to determine which cells to format
4. Use this formula =COUNTIFS($A$3:$A$15,$A3,$D$3:$D$15,$D3)>1
5. Click on Format
6. Select a Fill Color
7. Click Ok

8. Click Ok again

You will see Excel highlighted all the duplicate records where Name and Salary is same.

B) Through Formula:

1. In E3 cell enter below formula:
=COUNTIFS($A$3:$A$15,$A3,$D$3:$D$15,$D3)
2. Copy down the formula to $D$15 cell

You will see that, The record which has Number 2, is a duplicate record.

Remove Duplicates Excel Shortcut:

The shortcut key of Remove Duplicate command is:

ALT + A + M

Excel Remove Duplicates Formula:

1. In our data table use this formula and copy down to row 15:
=COUNTIFS($A$3:$A3,$A3,$D$3:$D3,$D3)
2. In this formula the number 1 is the unique record and 2 or more than 2 numbers are duplicate.

Remove Duplicates Not Working Trouble Shooting:

A) Number Problems:

Make sure that, your number is correct. To do this, use a simple TRUE FALSE check. For example: if =D3=D5 returns FALSE, then check that number may contains decimal like 1.0000000001 and that is why =1=1.0000000001 returns FALSE. So, check properly.

B) Text Problems:
Make sure that, your number is not TEXT. If your number contains an extra space or non printed character, then =64="64 " will return FALSE. Because Non Printed Characters or Extra Space converts your Number to a TEXT.

Video Tutorial:

Learn How to Use AutoFill in Excel to Save Time on Worksheet

2021-11-05T07:31:00.003+06:00

In Excel, you often work with a data table for data analysis. While working on data table, you my need to populate your data table through importing or manually entering them which is called data typing. It is true that, this will take time. But do you know that, there are few features in Excel which allows you to auto fill up your data table?. Yes, there are few features available in Microsoft Excel which allows you to auto fill your data table and save time while working on worksheet. Such as, One Input Data Table, Two Input Data Table, Goal Seek, AutoFill etc.

In this lesson, I'll help you to learn how to use AutoFill in Excel and save your time. It is very simple to use. Just a double click you need to do it. AutoFill is really a powerful tool in Microsoft Excel. It will populate your data table in seconds and save your time while working on worksheet.

AutoFill is a great feature in Microsoft Excel which allows you to fill series of numbers, dates and other data, create and use a custom lists. It will fill up to down or left to right with data automatically. It is very easy to use and you can use it quickly.

Use AutoFill for Different Data Types in Excel:

Using of AutoFill feature is completely depends on your data type and requirements. You can apply AutoFill Numbers for below data table in different ways in Excel:

1. Autofill a Series of Numbers:

There are different types of number on which you can apply AutoFill function. These are:

A. Data Related to Numbers:

This is very simple. It is 1, 2, 3, 4 and goes on. For example if you have an excel worksheet contains 13 Names and you want to make it a list by adding another column besides this column and make a serial, then follow below steps:

Step 1: Type 1 in C3 cell and 2 in C4 cell.
Step 2: Then Select the C3:C4 cell. You will see a small square box visible (it always visible when you are not in cell editing mode). This is called Fill Handle.
Step 3: Place your cursor on Fill Handle. You will see the cursor changed to a Plus ('+') symbol.

Step 4: Left Click on it and hold the Click (if you are right handed mouse user) then drag it down. You will see a magic. It automatically filled the serial in seconds.

Tips:
Autofill through Mouse Dragging allows Fill Up besides Fill Down. Example: 2, 1, 0, -1, -2 and goes on.

If you are unable to apply Mouse Dragging, then Excel has an alternative ways to use Autofill. To do this follow the below steps:

Step 1: Select C3:C15 cell range.
Step 2: Click on Home | Fill | Series.
Step 3: Series dialog box will appear.

Step 4: Select Columns from "Series in" section as you are trying to filling down automatically, within a column.
Step 5: Select AutoFill from Type.
Step 6: Click on Ok.

Tips:
In Step 5, you can also Select Linear and set the Step Value 1 in the dialog box and Click on Ok.

B. Data That Not Related to Numbers:

If the data is not related with numbers, it also can autofill. For example, if you have an excel worksheet contains 2 Items "First Name 1" in D3 cell and "Last Name 1" in D4 cell and you want to make it a list down by adding more "First Name 2" and "Last Name 2" for 13 different person, Excel can do it. Although it is not a proper number, but it can automatically fill if you drag it. To do this follow the below steps:

Step 1: Select D3:D4 cell.
Step 2: Simply drag your mouse down like previous.

Now can you see that, it automatically filled the serial in seconds? It's very powerful tool.

C. Even Numbers:

Not only Microsoft Excel can fill automatically serial numbers, it can also be able to fill Even Numbers. For example if E3 = 2 and E3 = 4, then follow the below steps:

Step 1: Select E3:E4.
Step 2: Drag your mouse down.

It will auto fill 2, 4, 6, 8 etc all even numbers.

D. Odd Numbers:

It can also be able to fill odd numbers. For example if F3 = 1 and F4 = 3, then follow the below steps:

Step 1: Select F3:F4.
Step 2: Drag your mouse down.

It will auto fill 1, 3, 5, 7 etc all odd numbers.

E. Sequence Numbers:

You can solve puzzle questions through AutoFill like "What will be the next number". For example if G3 = 7, G4 = 10 and G5 = 16, then follow the below steps:

Step 1: Select G3:G5.
Step 2: Drag your mouse down.

It will auto fill 7, 10, 16, 20, 24.5, 29, 33.5, 38 etc.

2. Autofill Dates:

Not only AotuFill can automatically fills Numbers, but also it can autofills Days, Dates, Weekdays, Months, Years.

A. Autofill Dates:

If H3 = 5-Nov-21 then follow the below steps:

Step 1: Select H3 cells only.
Step 2: Drag your mouse down.

It will autofill 5-Nov-21, 6-Nov-21, 7-Nov-21, 8-Nov-21 etc.

Tips:
You can also do this by selecting H3:H15 cells and Select Linear from Type and set Step value as 1.

B. Autofill Day:

If I3 = Fri, then follow the below steps:

Step 1: Select I3 cells only.
Step 2: Drag your mouse down.

It will autofill Fri, Sat, Sun, Mon etc.

C. Autofill Month:

If J3 = Jan, then follow the below steps:

Step 1: Select J3 cells only.
Step 2: Drag your mouse down.

It will autofill Jan, Feb, Mar, Apr etc.

3. Autofill Formulas:

AutoFill can also be fill down your formulas. If K3:K10 contains =COUNTIF($B$3:B3,B3) formula and you need to copy down the formula where cell will automatically changed within the column, then do the following:

Step 1: Select K3 cell and Drag your Mouse.

Tips:
Use Ctrl + D Shortcut command for formula copy down in Microsoft Excel.

4. Autofill Cell Formatting:

Autofill can fills automatically cell formatting. For example, if L3 cell is Bold and Fill Color Red, Font Color White. And you need to copy down this cell formatting then do the following:

Step 1: Select L3:L15
Step 2: Press Ctrl + D

Tips:

If you apply Ctrl + D, then cell Formatting or Formula will copy down. But if you use Mouse Drag then it will keep all including Autofill Numbers, Dates and others.

5. Autofill on Filtered Data

Autofill also supports on Filtered data. For example, if M3:M15 = 15-Nov-21 to 19-Nov-21 and you have already applied a filtered (visible rows) on 16-Nov-21 and 18-Nov-21, and you want to apply a Autofill for only M3 and M5 cell, then do the following:

Step 1: Select M3:M15

Step 2: Apply Filter by pressing Ctrl + Shift + L.
Step 3: Select 16-Nov-21 and 18-Nov-21.

Step 4: Apply a Fill color (for example yellow) in M3 cell.

Step 5: Drag your Mouse down.

The result will be like below image:

Now if you release the filter, then you will see that, others cell are not filled by yellow color. Than means Excel Autofill can also be use on filtered only data.

6. AutoFill with A Custom List

Yes, you can create your own custom AutoFill list. For example if you want to create a custom list "A, B, C, D to Z" which is like others already created in Microsoft Excel default list "Jan, Feb, Mar" or "Sat, Sun, Mon", then follow below steps:

Step 1: Click the File tab choose Options, Advanced tab, scroll down to the General section, then click the Edit Custom Lists button.

Step 2: Custom Lists dialog box will appear.

Step 3: Click on New List from Custom lists option. A cursor will appear in List entries section.

Step 4: Type values with Comma (,) to separate cell value. Type A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z.

Step 5: Click on Add button. It has now created and will show in Custom Lists box.

Step 6: Click on Ok.

To apply this Custom List, type A in N2 cell. Select the N2 cell and copy down. See the magic. By this way you can add many more of your own list. Don't worry, you can delete the unnecessary custom list.

Trouble Shooting about AutoFill Not Working:

1. Alphabetical letters are keep repeating, not working:

In Microsoft Excel, there are some default custom lists. These are:
(a) Jan, Feb
(b) Fri, Sat
(c) January, February
(d) Friday, Saturday

If you try outside of this custom alphabetical list, then Microsoft Excel will unable to help you automatically fill up your requirements. To do this you need to add them first. Please follow our How to add a Custom List in Microsoft Excel tutorial section in this article to add your missing Custom list.

2. While I place my Mouse Pointer over Fill Handle (small autofill square), it is not changed to "+" symbol and I'm unable to use AutoFill:

If your Mouse Pointer is not changing to "+" symbol, then you can Enable it by following below steps:

Step 1: Click on File | Options | Advance
Step 2: In the Editing options section, Check "Enable fill handle and cell drag-and-drop.

This will enable the "+" symbol for your AutoFill.

Video Tutorial:

3 Different ways to Hide or Unhide Rows and Columns in Excel 2016

2021-10-29T12:49:00.001+06:00

While working on large data table you may need to hide single row or some rows and single column or columns. In Microsoft Excel this is very common operational activities for all types of users. This feature allows you to hide less important rows or columns and keep your focus on important data area. On the other hand, if you need to update source data, set a new column or row formatting for your working area, or share your data table to others, then you may need unhide or show all the hidden rows or columns. So, it's good if you learn it.

This tutorial will teach you 3 different ways about how you can hide or unhide rows and columns singly or multiple in your Excel worksheet. As well as, after hiding rows or columns, I'll also share 3 different ways about how you can unhide the hidden rows or columns in Excel worksheet.

Start from Select Rows and Columns:

Before going to start our lesson, first off all you need to select the rows or columns perfectly. In this section you will learn how to select rows and columns in excel perfectly.

1. Single Row Selection:

A. Mouse Clicking:

Move your Cursor on any Row Number. Your Mouse Icon will change to Right Arrow. Then use your Left Mouse Click (for Right Handed Mouse user) on the Row Number to select that entire single row. Or Select any cell by clicking. Assume that you wish to select Row number 7, then Click on 7 from Row Numbers. Or click A7, D7, AZ7 or any.

B. Keyboard Shortcut:

If you wish to select the same row number 7, you can also perform it through Keyboard Shortcut. Move your cursor at Row number 7 of any Column, then press Shift + Spacebar at once to select that entire row.

C. Name Box:

If you are unable to click on the Row Number 7 or even you are unable to place your cursor at any cell of row number 7 because of row height is very short, then you can take help from Name Box. Just type the cell address you want to place your cursor there. In this example, you need to enter A7 or D7 or any column name with mentioning row 7 and press Enter.

Info:

A Name Box is a feature, by which you can select any specific cell address

2. Multiple Continuous Rows Selection:

A. Mouse Clicking:

Click on the Row Number from where you want to start selection. For example 7. Press and hold Shift key. Click on the selection ending row number for example 16. This will select entire Row 7 to Row 16.

B. Keyboard Shortcut:

Place your cursor at Row 7. Press Shift + Spacebar to select entire Row 7. Release the Spacebar only and then press the down arrow key from Cursor Key Set or Arrow Keys.

C. Name Box:

From Name Box, just type C7:C16 and press Enter to select rows 7 to 16.

3. Multiple Random Rows Selection:

A. Mouse Clicking:

Click on Row Number from where you want to start selection. Press and hold Ctrl key only. Then Click on another few Row Numbers randomly. After finish the random clicking, release the Ctrl Key. Example, first Click on Row 11. Then Press and hold Ctrl key. Click on Row 5, Row 19, Row 14. And Then release Ctrl Key.

B. Keyboard Shortcut:

Place your Cursor at Row 8. Press and hold Shift key and then Spacebar to select entire Row Number 8. Release Shift and Spacebar key. And Press and hold Ctrl key. Click on Row 5, Row 14. Release the Ctrl Key. Now Press Shift + Spacebar to select them.

C. Name Box:

From name box, Type B14,B17,B19 and press Enter to select these cells. Then press Shift + Spacebar to select entire last Row 19. Then release Shift + Spacebar key. Click on B17 and press Shift + Spacebar to select Row 17. Do it again for 14.

4. Single Column Selection:

A. Mouse Clicking:

Use your Left Mouse Click (for Right Handed Mouse user) on the Column Name to select that entire single column. Assume that you wish to select Column Name D, then Click on D from Column Name.

B. Keyboard Shortcut:

If you wish to select the same Column Name D, you can also perform it through Keyboard Shortcut. Move your cursor at Column D, then press Ctrl + Spacebar at once to select that entire column.

C. Name Box:

If you are unable to click on the Column Name D or even you are unable to place your cursor at any cell of Column Name D because of column width is very close, then you can take help from Name Box. Just type the cell address you want to place your cursor there. In this example, you need to enter D1 or D11 or any Row Number with mentioning Column D and press Enter to select Column D.

5. Multiple Continuous Column Selection:

A. Mouse Clicking:

Click on the Column Name from where you want to start selection. For example Click on Column D. Press and hold Shift key. Click on the selection ending Column Name F. This will select Column D to F.

B. Keyboard Shortcut:

Place your cursor at Column D. Press Ctrl + Spacebar to select entire Column D. Release the Spacebar only and then press the Right Arrow key from Cursor Key Set.

C. Name Box:

From Name Box, just type D7:F7 to select Column D to Column F.

6. Multiple Random Columns Selection:

A. Mouse Clicking:

Click on Column Name from where you want to start selection. Press and hold Ctrl key only. Then Click on another few Column Name randomly. After finish the random clicking, release the Ctrl Key. Example, first Click on Column D. Then Press and hold Ctrl key. Click on Column G, Column F, Column I. And Then release Ctrl Key.

B. Keyboard Shortcut:

Place your Cursor at Column D. Press and hold Ctrl key and then Spacebar to select entire Column D. Release Ctrl and Spacebar key. And Press and hold Ctrl key. Click on Column G, Column F. Release the Ctrl Key. Now press Ctrl + Spacebar to select them.

C. Name Box:

From name box, Type D14,B14,F14 and press Enter to select these cells as well as column. From Menubar Click on Home - Format and apply commands.

How to hide Rows in Excel:

There are 3 different ways to hide Rows in Excel. In this section, I would like to share how you can hide Rows by Mouse Clicking, from Menubar and from Keyboard Shortcut.

A. Hide Rows by Right Mouse Click:

Select the row or rows. For example 5, by clicking on Row Header. Or You can learn different selection ways as described earlier in this article.
Right Click on that Row Header (Row number 5).
A popup menu will appear.
Click on Hide.

B. Hide Rows from Menubar:

Place your cursor in a single cell. For example B6.
Goto "Home - Format - Hide & Unhide - Hide Rows".

C. Hide Rows from Keyboard Shortcut:

Place your cursor in a single cell. For example C7.
Press and hold Ctrl key and then Press 9 (Ctrl + 9).

How to Unhide Rows in Excel:

There are 3 different ways to unhide Rows in Excel. In this section, I would like to share how you can unhide Rows by Mouse Clicking, from Menubar or Ribbon and from Keyboard Shortcut.

A. Unhide Rows by Right Mouse Click:

Select the row or rows. For example 5. By clicking on Row Header from Row 4 to Row 6 to select the previous and after hidden row. Or you can learn different selection ways as described earlier in this article.
Right Click on that Row Header.
A Popup Menu will appear.
Click on Unhide.

B. Unhide Rows from Menubar:

Place your cursor in a hidden row cell. For example B6. You can take help of Name Box to place your cursor in a hidden cell as discussed earlier.
Goto "Home - Format - Hide & Unhide - Unhide Rows".

C. Unhide Rows from Keyboard Shortcut:

Place your cursor in a single cell. For example B6.
Press and hold Ctrl + Shift key and then press 9 (Ctrl + Shift + 9).

How to Hide Column in Excel:

There are 3 different ways to Hide Columns in Excel. In this section, I would like to share how you can Hide Columns by Mouse Clicking, from Menubar and from Keyboard Shortcut.

A. Hide Columns by Right Mouse Click:

Select the Column or Columns. For example C, by clicking on Column header.
Right Click on that Column Header (Column C).
A popup menu will appear.
Click on Hide.

B. Hide Columns from Menubar:

Place your cursor in a single cell. For example D6.
Goto "Home - Format - Hide & Unhide - Hide Columns".

C. Hide Columns from Keyboard Shortcut:

Place your cursor in a single cell. For example F7.
Press and hold Ctrl key and then press 0 Zero (Ctrl + 0).

How to Unhide Columns in Excel:

There are 3 different ways to unhide Columns in Excel. In this section, I would like to share how you can unhide Columns by Mouse Clicking, from Menubar and from Keyboard Shortcut.

A. Unhide Columns by Right Mouse Click:

Select the Columns. For example C4. By clicking on Column Header from Column B to Column D to select the previous and after hidden columns. Or You can learn different selection ways as described earlier in this article.
Right Click on that hidden Column Header.
A popup menu will appear.
Click on Unhide.

B. Unhide Columns from Menubar:

Place your cursor in a hidden column cell. For example G6. You can take help of Name Box to place your cursor in a hidden cell as discussed earlier.
Goto "Home - Format - Hide & Unhide - Unhide Columns".

C. Unhide Columns from Keyboard Shortcut:

Place your cursor in a single cell. For example E7.
Press Alt - o - c - u one after another (not at once).

Info:

Ctrl + Shift + 0 Shortcut key will not work in Excel to Unhide Columns. You need to use ALT - O - C - U one by one.

Video Tutorial:

How to Freeze or Unfreeze Rows and Columns in Excel 2016

2021-10-24T08:43:00.007+06:00

Generally when you are working with large data table in Excel, you have to move your cursor up down, left right often. If you not use Freeze, then it would be difficult for you to recognize the field title or row header. If you use Freeze feature then it becomes very easy to identify the row and column header and for this you can work smoothly on your large data table even you move from one area to another area of the same data table.

In this tutorial I've tried to present how to lock some areas of rows and columns as Row or Column Title or Header to visible always while scrolling in Excel Worksheets. This feature is known as Freeze in Microsoft Excel. This feature allows you to keep freezes rows and column visible while you navigate to another area of the same worksheet.

You will find the detail steps on how you can freeze one row or multiple rows, freeze one column or more columns, and freezing rows and columns at once.

How to freeze top row in Excel:

This is very easy to apply in your data table. Just follow the below steps:

1. Click on View tab
2. Click on Freeze Panes down arrow
3. Click on Freeze Top Row

A "Grey line" marked the lower border of your very first row.

Now move your cursor down of the large data table (minimum 2 pages) to see the magic of Freeze Top Row. Freeze Top Row locked your very first row in your current data table so that it is visible even you scroll down of the same data table.

How to freeze multiple rows in Excel:

If you want to freeze few rows from top, then first place your cursor in next row and apply the "Freeze Panes" option. For example, if you want to Freeze Row Number 1 to 2, then follow below steps:

1. Place your Cursor in Row number 3
2. Click on View tab
3. Click on Freeze Panes down arrow
4. Click on Freeze Panes

Again a "Grey line" marked the lower border of Row Number 2. If you move your cursor down of the large data table, you will see the Top 2 Rows are locked and always visible while you are moving your cursor down to the large data table.

Note:

* Microsoft Excel allows you to visible few Top Rows as header. It will not allows you to make freeze some rows from middle or at the end of your data table.

* Also Microsoft Excel Freeze Top Row feature will be different for your device view area. The more wider your display, the more top rows you can Freeze from top of the data table.

How to Freeze First Column in Excel:

To lock the first column of your data table for visible while you are moving your cursor at various point in the same data table, follow the below steps:

1. Click on View tab
2. Click on Freeze Panes down arrow
3. Click on Freeze First Column

A "Grey line" marked as the ending line of your very first column "A".

Now move your cursor right of the large data table. See, Freeze First Column feature locked your very first column in your current data table so that it is visible even you scroll right of the same data table.

How to freeze multiple columns in Excel:

If you want to freeze few columns from left, then first place your cursor in next column and apply the "Freeze Panes" option. For example, if you want to Freeze Column A to Column B, then follow below steps:

1. Place your Cursor in C1 cell
2. Click on View tab
3. Click on Freeze Panes down arrow
4. Click on Freeze Panes

Again a "Grey line" marked as the ending line of Column B. If you move your cursor right of the large data table, you will see the Column A and Column B are locked and always visible while you are moving your cursor right of the large data table.

Note:

* Microsoft Excel allows you to visible few Left Columns as header. It will not allows you to make freeze some columns at the middle or right side.

* Also Microsoft Excel Freeze Top Row feature will be different for your device view area. The more wider your display, the more top row you can Freeze from top of the data table.

How to freeze rows and columns at once in Excel:

This is the most commonly used Microsoft Excel Feature. You can freeze Row or some Rows and Column or some Columns at the same time besides freezing rows and columns separately.

For example, if you want to freeze Top 2 Rows and Very First 2 Columns at once, then here is the steps how you can do it:

1. Select C3 Cell.

Reasons behind selecting C3 cell:
* To freeze top row (Row number 2), place the Cursor at A3 cell.
* Now to freeze very first 2 columns (Column "A" and "B"), move your Cursor from A3 cell to C3 cell.

2. Click on View tab
3. Click on Freeze Panes down arrow
4. Click on Freeze Panes

This time the "Grey" line will mark or separate the Row Number 1 and Column A. That means these are locked and will visible while you move your cursor right or down of the same data table.

By following these "Reasons behind selecting C3 cell" you can freeze as many rows and columns as you need. If you select D3 then, Column A to Column C and Row number 1 to Row number 2 will freeze.

How to Unfreeze Rows and Columns in Excel:

This is very simple. If your data table set as frozen, then first you need to Unfreeze it. Here is how you can do it:

1. Click on the View tab
2. Click on Freeze Panes down arrow
3. Click on Unfreeze Panes

Note:

If you don't know which freeze feature already has set, don't worry. Just Click on the Freeze Panes down arrow. There you will see the "Freeze" name changed to "Unfreeze". Now just click on "Unfreeze Panes" to unfreeze.

Alternative way to Lock the Top rows in Excel:

1. Select your data table.
2. Press Ctrl + T to convert your data table to Table format.

Trouble Shooting:

(A) Freeze Panes greyed out or disabled:

While you are going to use the Freeze Panes option, and you found that the Freeze Panes options are "greyed out" or disabled, then below might be the reason:

1. You are in Editing mode (your cursor is blinking in the cell and waiting for your input). Press Enter or Esc button to exit from Edit Mode.

2. You Microsoft Excel Worksheet Protected. You need to make it protected it first.

(B) Excel data table is not showing Cursor while I move it on data table:

1. That means this worksheet freezes at that point where your display is out. Just unfreeze it.

Video Tutorial:

How to freeze or Unfreeze Rows and Columns in Excel 2016 from Faysal Easy Excel YouTube Channel:

How to change the row height and column width in Excel 2016

2021-10-22T08:44:00.005+06:00

While working on a data table, it needs to adjust the cell height and width depending on cell values for making data table easy to work with. Microsoft Excel has more than one different ways to change the column width and the row height. You can change it through Mouse Pointer or from Menu feature in seconds. The most easiest way is double clicking. I'll try to present here all the ways, so that you can choose your best.

In this tutorial, you will learn how to change the row height and column width in Excel 2016. I've tried to present here 3 different ways for rows height and column width separately. You can choose the most efficient ways for you to use it in your worksheets.

Change Row Height:

In Microsoft Excel, the default row height is determined by the font size. As you increase or decrease the font size for a specific Row or Rows, Excel automatically changes the row height. So, depending on cell value (font size, wrap text, line break text) you can adjust your Row Height in 3 most common ways. The details are given as below:

A. Change Row Height with Double Headed Arrow:

1. Select the Row or Rows you want to change the height.
2. Place your Mouse Point over the lower boundary of the row heading (in between row numbers line, so that Your Mouse pointer changed to Double Headed Arrow) and double click the border line.

Tips:

While you click and hold the Double Headed Arrow, Microsoft Excel shows the points. Depending on your mouse movement it increases or decreases the row height point.

B. Change Row Height with Numerical Value:

As mentioned in earlier Tips, Excel row height is specified in points. So, you can adjust a row height by changing it's points manually. To do this follow below steps:

1. Click on the Home tab.
2. Click on Format from Cells Group.
3. Select Row Height.
4. In the Row Height dialog box, type the desired point number or value
5. Click OK to save the change.

C. Change Row Height with Double Click:

As mentioned in earlier "A", if you double click while your mouse pointer changes to "Double Headed Arrow" instead of Click and hold and move up and down, then it will automatically detects each row heights separately.

Change Column Width:

In Microsoft Excel, the default column width is determined by the length of cell value. As you add more values in a single cell Excel automatically changes the column width to show the full cell value in one line. So, depending on cell length, you can adjust your Column width in 3 most common ways. The details are given as below:

A. Column Width with Double Headed Arrow:

1. Select the Column or Columns you want to change the width.
2. Place your Mouse Point over the right boundary of the column heading (in between columns named line, so that Your Mouse pointer changed to Double Headed Arrow) and double click the boundary line.

Tips:

While you click and hold the Double Headed Arrow, Microsoft Excel shows the points. Depending on your mouse movement it increases or decreases the column width point.

B. Change Column Width with Numerical Value:

As mentioned in earlier Tips, Excel column width is specified in points. So, you can adjust a column width by changing it's points manually. To do this follow below steps:

1. Click on the Home tab.
2. Click on Format from Cells Group.
3. Select Column Width.
4. In the Column width dialog box, type the desired point number or value
5. Click OK to save the change.

C. Change Column Width with Double Click:

As mentioned in earlier "A", if you double click while your mouse pointer changes to "Double Headed Arrow" instead of Click and hold and move Right and Left, then it will automatically detects each column widths separately.

Video Tutorial:

Remove spaces between words or numbers in Excel cells

2021-10-21T17:37:00.005+06:00

There are Many ways to remove extra spaces but in this tutorial I'll share 2 most common ways to remove spaces between words or numbers in Excel cells. These are: TRIM formula, Excel Find and Replace options.

Why it happens:

When you copy paste data from an external source to Excel then it can be happens. For example, if you copy a data table from Web Based Software or Web Page and then Paste them to an Excel Worksheet, then an extra space will add along with your each cell values. This space usually add at the end of the cell values, but you can also find them at the beginning or in the middle point of a cell value.

For this additional spaces your data table looks disorderly and it becomes difficult to work with. For example if you use Find option to find a First Name "Bill Jillen" which has no extra spaces, then it will not found in your data table.

Thus it is very important to remove spaces between words or numbers in Excel cells to do good data analysis.

The Solutions:

In this article you will find how to clean up your data.

Way 1: TRIM Function:

Trim function removes "printable" extra spaces from a cell value. for example, below table table contains 2 columns. In the column Name, the first cell contains "Bill Jillen" written correctly without excess spaces. All other cells have extra blanks between the first and the last names. At the same time these cells have irrelevant blanks before and after the full names known as leading and trailing spaces. The second column "LENGTH" is showing how many "printable" character used in first column.

Use the Trim formula to remove extra spaces in this way:

1. Create an extra column and use Trim formula =TRIM(A4) as first name contains in A4 cell.

2. Drag the mouse to Copy down the formula in C Column.

3. See the result in C column. Copy the results and use Value Paste in A4:A7 cell.

Way 2: Use Find and Replace option:

The Find and Replace option is also a faster way to remove extra spaces in numbers or text in a cell. To do this you need to follow below steps to remove extra spaces:

1. Select A11:A14 cell range

2. Press Ctrl + H to show "Find and Replace" dialog box.

3. Enter Space twice in "Find what" option.

4. Click on "Replace all" button, and then Click on Ok.

You may also like:

Video Tutorial: Remove spaces between words or numbers in Excel Cells:

Selecting Cells and Ranges of a Data Table in Excel 2016

2021-10-20T18:10:00.001+06:00

A device of computer is Mouse by which you can easily select, drag (Press Left Click of a Mouse and Hold it and then move it over a Data Table) cells of a Data Table. But for faster and work like a professional, you can also use Keyboard Shortcut. In this tutorial I've tried to share 8 different cursor movements for Selecting Cells and Ranges of a Data Table in Excel 2016. If you practice these, then it's good go for next level.

Faysal Easy Excel Video Tutorial of Selecting Cells and Ranges in Excel

One: Select Single Cell:

To select any of a single cell of a data table, just Left Click (Right Handed Mouse User) on that cell. Now this cell has selected. You can apply any formatting to test it.

Two: Select More cells in a Range:

Most of the Excel users will use Mouse Left Click + Drag to Select more than single cells in a range of a data table. But if you are not keen on using the mouse, you can use the keyboard to select the cells you want. Press and Hold the Shift key and then Press Arrow Keys to select a range.

Three: Select Specific few cells:

Press and hold Ctrl key and then Left Click specific random cells to select them.

Four: Select Entire Row:

To select entire row, Left Click on Row Number or Select any cell of that row and press Shift and Space Bar at once.

Five: Select Entire Column:

To select entire Column, Left Click on Column Name or Select any cell of that Column and press Ctrl and Space Bar at once.

Six: Select Entire Data Table:

Click anywhere of a data table and press CTRL + A (Select All).

Seven: Select Header Row Only:

Click on first cell and then press and hold Shift + Ctrl + Right Arrow Key at once.

Eight: Select First Column Only:

Click on first cell and then press and hold Shift + Ctrl + Down Arrow Key at once.

There are some others Keyboard Shortcuts are available. But these are the basic for beginners. In the future I'll try to share more. Stay tuned.