ICDL-EXCEL outline - The ICDL L4 excel Updated: 2023

When you select the square box, it highlights all the cells in the worksheet even the empty cells as well. As an alternative, you can also use the Ctrl+A keyboard shortcut to select all the cells on the worksheet.

Read: Microsoft Excel Tutorial for Beginners

2] Autofit Column width

While working on the MS Excel with a large amount of data record, often it becomes complicated to resize each column cells according to its text. In order to resize the columns in an easy way, you can use the AutoFit column width feature that is an inbuilt feature in Excel.

All you need to move your mouse pointer to the boundary of the column you want to autofit and then double click on it. And if you have to adjust the entire columns then select them all and double click on the boundary of any column.

Alternatively, you can also resize the column width by using the Autofit Column width option. To do this, select the column you want to adjust and then navigate to the Menu bar > Home tab. In the Cells section, click on the Format option and then select Autofit Columns Width from the drop-down menu.

Read: List of best free Excel Add-ins you should be using

3] Transpose columns and rows

Some people create Excel spreadsheets where the main fields go across horizontally in a row. Others put the data vertically. Sometimes these preferences lead to a scenario where you want to transpose Excel data. Transpose is an Excel function that allows you to switch Excel columns to rows without retyping the data.

Transpose function is a great feature you will get in the MS Excel spreadsheets. It becomes very helpful when you need to switch the data columns from horizontal to vertical without retyping the data.

In the above picture, the data are arranged in a tabular form where the figures of the subjects are showing vertically. If we need to rearrange the data horizontally in a manual way then it would be quite difficult. But with the help of transpose function, you can do it in a couple of seconds.

So, you first need to select the data from the table and copy them. Now, place your mouse pointer where you have to transpose the data, do a right-click and select Paste Special option from the context menu. When the Paste Special window opens, check the Transpose function and click on the OK button.

Read: Top 15 Financial functions in Microsoft Excel

4] Use Filter Command to Hide Unwanted Data

Excel filter function allows you to hide the unwanted data and to display only certain important data in the spreadsheet. Using this feature, you can easily filter the names, dates, and numbers from the huge dataset or table. However, it doesn’t remove or modify the original data. It becomes very useful when you need to focus only on some special kind of information.

In the above dataset, you can see there is a list of students whose marks are different, their address is different. It can be large even more. So, if you need to filter any specified data then it can be quite difficult.

To make it in an easy way, you can use the filter command. So, all you need to select the header of the table and then navigate to the Editing section. In the corresponding section, select the Sort & Filter and then choose the Filter tool.

Click on the down-arrow option that comes to appear near the header of each column. It allows you to sort your data in alphabetical order or to arrange the numbers by smallest to largest. Furthermore, you can use the text filter to filter the data as per your requirement.

5] Remove Duplicate Entry

It is quite an easy job to locate and remove duplicates entries for a spreadsheet having a small set of data. But if it has to be done with a large dataset where the columns and rows are in hundreds then it would be indeed a challenging task.

In the above table, there are ten records in which two entries are totally similar and a common name has been used in three rows. So, in order to find out the duplicate entries, you need to select the table. After that, navigate to the Data menu, select the Data Tools category, and then click Remove Duplicates.

In the dialog box, mark the checkbox near the My data has headers. And, under the columns section, remove the mark of the serial number as it is different for each entry. Finally, click on the OK button and you will see the duplicate entry gets removed from the table.

6] Remove the Blank Records

Sometimes you might have noticed the blank cells or rows in your spreadsheet that are mostly undesirable. Users commonly do a right-click on the specified row and delete them one by one. But what will you do if their numbers are in hundreds? In that situation, deleting them one by one would really be a tedious job.

Therefore, in order to delete them in one go, press Ctrl+G shortcut key and when the Go To dialog box comes to appear, select the Special button. On the following page, mark the Blanks radio button and click on the OK button. You will notice that all the blank cells get selected.

Now, press the Ctrl+minus button. When a pop-up menu appears, select Shift cells up and click on the OK button. You will see, all the blanks cells get removed and the dataset also arranged suitably.

7] Insert Blank Rows into Record

As I aforementioned that blank rows are mostly undesirable but sometimes it works as well. So, if you need to insert them, you can do a right-click on the specified row and add them one by one as I stated while removing blank rows.

So, to insert the blank rows in each separate record, make a series of the mentioned serial number. After that, select all the data set and then click on the Sort tool available on the Data tab.

In the following dialog box, click on the Sort by drop-down menu, and arrange them by serial number.

Clicking on the OK button, it will insert a blank row between each separate row.

8] Copy Data to Another Sheet Automatically

Sometimes it happens that you need to use the data in another sheet which you enter in the first sheet. To do so, you commonly use the traditional copy and paste method. But, when you need to change some data in one sheet then it again has to change it on the other sheet as well. It becomes a bit complicated when you have to change the same data on both sheets one by one.

In excel, there is an option that automatically copies the data of the first sheet into other sheets in a real-time process. However, for copying the data automatically to other sheets, you need to first make a connection between them.
To do so, go to the status bar of the first sheet. After that, keep pressing the Ctrl key and click those sheets on which you have to copy the data automatically. That’s it.
Now whatever data you enter on the first sheet, it automatically gets copied on the other sheets.

9] Take Screenshot using Excel Camera

It has always been said that “A picture is worth a thousand words”. Accordingly, sometimes you may need to add a screenshot within your Excel worksheet so that you could explain the things in a better way.

However, you don’t need to use any third-party application to capture a screenshot. Excel has an inbuilt camera command that helps you to take the screenshot in a single click.

To use this feature, you first have to enable the camera command. So, all you need to navigate to the quick access toolbar and click on the Customize Quick Access Toolbar. After that, click on the drop-down menu of Choose commands from > All commands > Camera. Now, select the Add button and hit OK.

After following the above procedure, you will notice a Camera icon is now added to the quick access toolbar. Now, select the cells or data for which you need an image. After that, click on the Camera icon and then click anywhere on the worksheet. You can also drag and resize the image to adjust according to your requirements.

10] Link the cells to your email address

In the excel worksheet, you can easily create a hyperlink to your Gmail address.

To do so, you first need to select the cell where you want to insert the link. After that, press Ctrl+K shortcut. When the dialog box prompts on the screen, select the E-mail Address from the left pane. Add your email address and then hit the OK button.

That’s it. I hope these tips and tricks may be helpful while using Excel worksheets on your computer.

Now read: Advanced Microsoft Excel Tips and Tricks for Windows users

The error messages that are most associated with this runtime error are as follows:

• VB: run-time error ‘1004’: Application-defined or object-defined error
• Excel VBA Runtime error 1004 “Select method of Range class failed”
• runtime error 1004 method range of object _global failed visual basic
• Excel macro “Run-time error ‘1004?
• Runtime error 1004 method open of object workbooks failed
• Run-Time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
• “Method in Key up Object Program APPLICATION Failed.”
• Run-time error ‘1004’: Activate method of Worksheet class failed

If you encounter any of these above errors, then you can fix the error using our guide.

What are the causes?

The Error 1004 is a general code related to MS Excel but is not specific to one exact cause. Hence, in this case, the exact reason why this error might pop up will vary from case to case and circumstance to circumstance. From configuration issues to software problems, below we listed a synopsis of common reasons for runtime error 1004 in excel:

• MS Excel Desktop Icon might be corrupted
• VBA Excel File is clashing with other application
• Due to application or object specified error
• Due to missing dependent file
• Due to Virus, Trojan or malware
• Due to Invalid Registry Keys and so on.

These were a few of the most common reasons behind getting the runtime error 1004 in MS Excel; now let us understand the different fixes.

Here we have detailed both manual and automatic solutions to fix the Runtime Error 1004. You can follow any one of the next methods to resolve the issue.

1. Create a new Excel template
2. Launch Excel in Safe Mode
3. Run a Virus Scan
4. For VB: run-time error ‘1004’, resize legend entries

Let’s look at each of these methods in detail.

1] Create a new Excel template

In some cases, fixing this issue can be as simple as inserting a new worksheet from a template instead of creating a copy of an existing worksheet. Here is what you need to do:

1] Open MS Excel on your system

2] Press ‘CTRL + N’ to create a new Microsoft Excel worksheet or simply select a ‘Blank workbook’ from the first screen.

3] Once done delete all the sheets on the workbook except one.

4] Now, format the workbook, which has been left. Also, note, this workbook can be modified to suit your individual need.

5] In the end, go to ‘File > Save As’ to save the new worksheet with the Excel Template (.xltx or .xlt) file format.

6] Once you have successfully created the template, you can insert it programmatically by using the following line of code:

Sheets.Add Type:=path\filename

Please note – Do not forget to replace the new filename with the genuine name of the document.

2] Launch Excel in Safe Mode

If Excel is showing you the runtime error 1004 after opening an Excel file, there might be a problematic add-in causing the issue. The affected users received the error message given below:

Run-time error ‘1004’: Activate method of Worksheet class failed

To check if the problem is caused by an add-in or not, launch Excel in Safe Mode. If Excel opens successfully in the Safe Mode, the problem was occurring due to an add-in. Now, you have to identify the problematic add-in. To do that, follow the steps provided below:

1. In Safe Mode, go to “File > Options > Add-Ins.”
2. Select COM Add-ins in the drop-down on the right side and click Go.
3. In Safe Mode, some add-ins are disabled. You have to enable the disabled add-ins one by one. Enable one of the disabled add-ins and restart Excel in normal mode. See if the error occurs.

If the error occurs, repeat the above steps again. After you find the problematic add-in, consider removing it from Excel.

3] Run a Virus Scan

It is very important to scan your computer system for malware and viruses as these can corrupt files and important documents and show the runtime error 1004 in MS Excel. Sometimes using a good antivirus program helps a lot.

Related: Run-time error 1004, Cannot run the Macro in Excel.

4] For VB: run-time error ‘1004’, resize legend entries

If you come across a Runtime Error 1004 when running a Microsoft Visual Basic for Applications (VBA) macro, then you can use this method to work-around.

Generally, you get this error when you try to run a VBA macro that uses the LegendEntries method to make changes to legend entries in a Microsoft Excel chart. That time, you may receive the following error message:

Run-time error ‘1004’: Application or object-defined error

This error occurs when the Excel chart contains more legend entries than there is space available to display the legend entries on the Excel chart. When this behavior occurs, Microsoft Excel may truncate the legend entries.

To work around this behavior, create a macro that reduces the font size of the Excel chart legend text before your VBA macro makes changes to the chart legend and then restore the font size of the chart legend so that it is similar to the following macro example.

Sub ResizeLegendEntries()
With Worksheets("Sheet1").ChartObjects(1).Activate
      ' Store the current font size
      fntSZ = ActiveChart.Legend.Font.Size
'Temporarily change the font size.
      ActiveChart.Legend.Font.Size = 2
'Place your LegendEntries macro code here to make
         'the changes that you want to the chart legend.
' Restore the font size.
      ActiveChart.Legend.Font.Size = fntSZ
   End With
End Sub

We hope this article helps you fix the runtime error 1004 in Microsoft Excel. This guide gives you manual as well as automatic solution to get rid of this error; you can make use of any solution based on your need.

How do I fix a runtime error?

Runtime errors occur at the time of running a program. The runtime errors usually freeze or crash the affected program or software. To fix a runtime error, you can use general fixes, like installing the latest Microsoft Visual C++ Redistributables, running the SFC and DISM scans, troubleshooting in a Clean Boot state, etc.

Read next: Arrows keys not working in Microsoft Excel.