Learn Microsoft Excel Online FreeWhen we launching Excel and create a new workbook, we can find that some worksheets are already created and the default sheet name is sheet1 for example. Actually, we have to rename worksheet name in most situations depends our requirement. And in our daily life we often rename worksheet name by right click or double… read more »
This post will guide you how to force test strings to uppercase or lowercase or Proper cases through an Excel formula or data validation in your Excel 2013/2016. How to convert text to upper, lower, or proper case in Excel. Assuming that you want to force text strings to Upper case when you type words… read more »
After editing current worksheet (workbook) we often want to send this workbook to others to share information or assign task directly by email. Though we can launch our Outlook mailbox, add it as attachment and then send it. Actually, we have other ways to send it through Outlook in excel directly. This article will introduce… read more »
Normally we enter a data only contains number in cell, it will be recognized as ‘Number’ in excel by default. For example, we enter ‘001’ into one cell, ‘00’ part will be dropped automatically after entering, and only ‘1’ is displayed. But if we add an apostrophe in front of number, it will be converted… read more »
Sometimes we create a table with formula applied to specific column. And when we editing the table, for example inserting a new row with data into the table, we want the new data lists in the specific column is also applied with this formula as well. Normally we can drag down the upper cell applied… read more »
In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can we do? Actually, we can still use VBA script or formula of Index function to… read more »
Sometimes we need to split data from a long column into a table with several columns based on some criteria like X rows or X columns. If we split data by manual, it is very complex and troublesome. So, we need a simple way to split data into multiple columns. This article will introduce you… read more »
If there are multiple worksheets exist in excel, there will be a lot of worksheet tabs in tab bar. And it is difficult to find a worksheet we want. Though we can search worksheet by right click in worksheet bar to activate worksheet, a simple and convenient way will be better especially there are a… read more »
Normally there are some redundant rows in worksheet and we have to remove them manually, and if there is a simple way to delete them by one step will be much better. If you want to delete rows under certain row or selected active cell, you will not miss this article. This article aims at… read more »
If there are some hidden worksheets exist in excel and you want delete them as they are not useful, you can unhide all worksheets and remove them one by one. This way is quite annoying and complex. If we can remove all hidden worksheets by only one step or a simple way, it will be… read more »
Sometimes we want to copy cell format to others to make cells or tables in unique format. We can check the cell format and then set the same format for the cells you want to apply the formatting, but this way is very complex and boring. We need some way simple and convenient to apply…. read more »
Sometimes we create a Macro but we don’t want trigger it by Run Macro via Developer->Run Macro or other keyboard shortcuts. We may set the trigger by clicking a specific cell like click A1 or E1 for example. Once we clicking on the specific cell A1 or E1, the Macro will run properly, it looks… read more »
Usually we often define worksheet tab name depending on the contents of this worksheet like Summary, Product List etc. But in some cases, we want to define the worksheet name equals to the text in specified cell, for example we want to the contents in A1 cell is also displayed as the worksheet tab name,… read more »
Sometimes we often need to add some characters or strings in uniform before or after the text in each cell. If we update cells by copy and paste the characters or strings one by one, it will cost a lot of time, and the work also seems very boring. So, we need to find a… read more »
In our daily work, in case of protect our worksheet from being edit by others, we often add a password for opening worksheet. But sometimes, the worksheet is open for all or shared with your partners, we just want to set some important cells as read only in case of preventing others to edit them… read more »
This post will guide you how to create a backup file of workbook in Microsoft Excel. How do I create or make the backup of your current workbook in Excel 2013/2016. How to create a backup of a workbook with the current date using VBA macro in Excel. Create Backup of Workbook with AutoRecover You… read more »
We often see some duplicate data in excel, and if we want to delete the duplicate data and keep only one copy, we need to find out all duplicate values from table and then remove them from table. This article will help you to know how can we find out these duplicate values and remove… read more »
If we inserted a large number of charts and want to delete all of them quickly how can we do? It will spend a lot of time if we delete them one by one in excel. So we need a quick way to delete them all conveniently. This article will provide you some methods to… read more »
Usually we create multiple worksheets in excel and we can switch between these worksheets to do different operations. But if we want to quickly get back from current worksheet to the previous operating worksheet , how can we do? Dragging the scroll bar back to the previous worksheet is hard to find the proper worksheet… read more »
Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest numbers in the range of data. As we know we can use some formula like… read more »
