Function Archives

3 Easy Methods to Transpose Data in Excel (Step-by-Step Guide)

Leave a Comment / Function / By Narendra Singh

Using Excel, you can enhance the appearance of your worksheet with a wide range of formatting methods. This article describes different methods for transposing Data in Excel. What is Transpose in Excel? Converting vertical datasets to horizontal ranges or vice versa, helps us sort unformatted data correctly. Using Transpose, you can reorganize an Excel worksheet …

3 Easy Methods to Transpose Data in Excel (Step-by-Step Guide) Read More »

How to Copy Data From a Closed Workbook in Excel VBA

Leave a Comment / Function / By Narendra Singh

This tutorial will explain to you the step-by-step procedure for pull or copy data from a closed workbook in Excel VBA. Here we have two different files one file will be closed and the second one will remain open. See the image below:- Our 1st file “NewData1” contain some dataset and with the help of …

How to Copy Data From a Closed Workbook in Excel VBA Read More »

9 Different Ways to Create New Sheets in Excel VBA

Leave a Comment / Function / By Narendra Singh

This blog post explains the multiple ways to create new sheets in Excel VBA. In Excel, when we create a new sheet, we generally use the keyboard shortcut “SHFT+F11”. And new sheet will be inserted by default and always before the active worksheet and its default sheet name i.e. Sheet2, Sheet3, Sheet4, and so on. …

9 Different Ways to Create New Sheets in Excel VBA Read More »

How to Convert Excel Files to CSV Format File

Leave a Comment / Function / By Narendra Singh

In this blog post, I will explain how to convert Excel Files to CSV format files. Here, we are going to use the below Excel dataset and we are going to convert this dataset into CSV (Comma Separated Value) format. See the image below:- First, go to the location of our dataset where we have …

How to Convert Excel Files to CSV Format File Read More »

How to Create Search Box in Excel (No VBA Code Required)

Leave a Comment / Function / By Narendra Singh

This blog post shows you to create search box in Excel without using VBA code. The search box will find all the duplicate or same cell values in one search. Suppose that we have a dataset and multiple cell values are the same within it. See in the image above where we circled cell values …

How to Create Search Box in Excel (No VBA Code Required) Read More »

How to Filter the Data as You Type in Excel VBA

Leave a Comment / Function / By Narendra Singh

This post explains how to filter the data as you type with the help of Excel VBA. Suppose that you have a dataset and you want to apply a filter in Excel with the help of VBA. So that you can filter the name and get them to filter the below dataset. Here in this …

How to Filter the Data as You Type in Excel VBA Read More »

VBA Macro to Dynamically Copy and Paste from One Sheet to Another

Leave a Comment / Function / By Narendra Singh

This blog post explains about to copy and paste from one sheet to another dynamically in Excel VBA. Here we have two worksheets “Sheet1” and “Sheet2”. We have some data in “Sheet1” and we want to paste it into “Sheet 2”. It needs to be copied under the existing dataset in “Sheet2”. So simple VBA …

VBA Macro to Dynamically Copy and Paste from One Sheet to Another Read More »

How to Lock Scroll Area in Excel with VBA (Step By Step Guide)

Leave a Comment / Function / By Narendra Singh

This blog post shows you the step-by-step procedure to set or lock scroll area in Excel. Suppose that you are working in a dataset and you do not want to allow the user to enter the data beyond that dataset. In that case, you can set the limit for the user to not enter the …

How to Lock Scroll Area in Excel with VBA (Step By Step Guide) Read More »

How to Add Plus Sign (+) Before Positive Number in Excel

Leave a Comment / Function / By Narendra Singh

We see the (-) negative sign before any negative number but do not find any (+) sign before any positive number.

How to Hide Blank Rows in Excel Using VBA Macro

Leave a Comment / Function / By Narendra Singh

This blog post shows you how to hide blank rows in Excel dataset using VBA. Blank rows are not good in any Excel dataset and may create trouble while creating reports. Assume we have a dataset that contains some blank rows. You should remove or hide these blank rows so that they will not cause …

How to Hide Blank Rows in Excel Using VBA Macro Read More »