Trending March 2024 # How To Repair A Corrupted Excel Workbook # Suggested April 2024 # Top 4 Popular

You are reading the article How To Repair A Corrupted Excel Workbook updated in March 2024 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Repair A Corrupted Excel Workbook

The biggest pain when working with files is when they get corrupt. While Office 365 offers a file recovery feature for all its file types, including Word, Excel, and PowerPoint, but if that doesn’t work, it’s a lot of rework. This post will guide you on how you can repair a corrupt Excel workbook and also place backup methods so recovery works better.

Repair & Recover a corrupt Excel Workbook

Follow these two methods to repair a workbook when they show up as corrupted. When you open an Excel file, in such cases, you will get a message that either the file is corrupt or it will not open, or there is no data in the file. Usually, Excel offers a recovery, but if that doesn’t work, you can use the recommended methods.

Repair a corrupted Excel workbook manually

Excel will offer you two options.

Recover Data (Repair)

Extract values and formulas from work when repair fails. (Extract Data).

Recover data from a corrupted workbook

Revert the workbook to the last saved version: If the file got corrupt while working on it, it is best to close it without saving it instead of trying to save it with corrupted data. Open it fresh, and it will take you to the state where everything was working.

Once done, you will need to reopen the file and save it as an Excel workbook. Since we are saving everything as a new file, the original file is still there.  The only drawback of this method is that it will only save the active sheet and not others.

Use a macro to extract data from a corrupted workbook: If the sheet has charts and uses data on the book as a data source, then this macro from Microsoft can help you recover that data.

Sub GetChartValues() Dim NumberOfRows As Integer Dim X As Object Counter = 2 ' Calculate the number of rows of data. NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values) Worksheets("ChartData").Cells(1, 1) = "X Values" ' Write x-axis values to worksheet. With Worksheets("ChartData") .Range(.Cells(2, 1), _ .Cells(NumberOfRows + 1, 1)) = _ Application.Transpose(ActiveChart.SeriesCollection(1).XValues) End With ' Loop through all series in the chart and write their values to ' the worksheet. For Each X In ActiveChart.SeriesCollection Worksheets("ChartData").Cells(1, Counter) = chúng tôi With Worksheets("ChartData") .Range(.Cells(2, Counter), _ .Cells(NumberOfRows + 1, Counter)) = _ Application.Transpose(X.Values) End With Counter = Counter + 1 Next End Sub

To execute the macro, create a new sheet in the Excel file. Then select the chart, and run the macro. The data will become available in the new sheet you created.

How to prevent Workbook corruption

While those methods help when you have a corrupt file, it is best to have a recovery method in place. These are some built-in methods you can enable to make sure you don’t lose files in the future.

Automatically save a backup copy of a workbook:

Check the box which says Always create a backup.

Then save the copy.

This will make sure a backup is there every time you want to recover the worksheet.

Automatically create a recovery file at specific intervals:

Under the Save category, configure the following.

Check the option Save AutoRecover information every and set up the time.

Setup AutoRecover file location to a place which you know

Uncheck Disable AutoRecover for this workbook only

I hope the post helped you recover and repair the corrupt Excel Workbook. Always make sure to set up the backup configuration of the files.

You're reading How To Repair A Corrupted Excel Workbook

The Workbook Cannot Be Opened In Excel Online

The Workbook Cannot Be Opened in Excel Online [Quick Fix] Your file size could be making Excel Online not to work

1

Share

X

Large file size could be the reason Microsoft Excel Online is not working on your browser.

You can fix the issue quickly by resetting your browser to default and clearing the cache.

Another effective solution is to download the file and re-save it to drive.

Struggling with various browser issues? Try a better option: Opera One

You deserve a better browser! Over 300 million people use Opera One daily, a fully-fledged navigation experience coming with various built-in packages, enhanced resource consumption, and great design.

Here’s what Opera One can do:

Optimize resource usage: Opera One uses your Ram more efficiently than Brave

AI and User Friendly: New feature directly accessible from the sidebar

Gaming friendly: Opera GX is the first and best browser for gamers

⇒ Get Opera One

Excel Online not working or not opening files is a common problem among Windows 10  users, but it has general workarounds that can resolve it.

However, for more specific issues, one would have to state the exact error they receive when working with the program.

As an example, multiple users reported a specific issue when they couldn’t open the workbook on Excel online because it exceeds a certain memory limit.

Other times, the issue appears if someone edits a workbook in Excel Online and then leaves the document open without making any additional changes for up to 6 hours. Plus, it might be down to worksheet protection or too many styles.

Irrespective of the cause, this guide contains proven fixes to solve the issue.

Why is Microsoft Excel Online not responding?

The Microsoft Excel Online not working is mostly down to browser issues. Also, it can be caused by the wrong settings on your workbook.

➡️ Below are some of the most prevalent causes:

Accumulation of corrupt browser data

Corrupted workbook files

Wrong browser settings

Large file size

For instance, you may encounter different error types like this: Sorry, we can’t open your workbook in Excel Online because it exceeds the 100 MB file size limit.

Another factor that causes the error can be that the workbook cannot be opened or repaired by Microsoft Excel because it’s corrupted.

Thus, you should first check these possibilities of Excel Online issues to identify the specific cause in your case.

How do you fix the Workbook Cannot Be Opened? 1. Use a different browser

If Excel Online is not working on some of the popular browsers like Chrome and Safari, it may be a good option to use a different browser. And there is no better alternative than Opera.

It’s a great error-free browser with amazing features that allow you to customize it based on your needs and help you save a lot of time.

Opera One

Change to a reliable browser with a fast connection and a flexible easy-to-customize interface.

Free Visit website

2. Reset the browser and clear the cache

This is a general fix for the Excel Online not working or loading issue and can be applied to other browsers like Chrome, Safari, and Firefox. Thankfully, most of these browsers have in-app options for resetting them.

Lastly, clearing your browser cache and cookies is a recommended fix if Excel Online is not working on Box.

3. Try Two-Step Verification

Two-step verification protects you by making it hard for anyone else to log in to your Microsoft account using two identification methods, including your password and contact or security information.

Whether someone else gets your password, they won’t be able to access your account without your security information. This is why having different passwords on all your accounts is important.

To set up this two-step verification, you will get a security code sent to your email, SMS via phone, or an authenticator app each time you log in on a device that is not trusted.

When this is off, you only need to verify your identity using the codes at different times, which is risky to your account.

4. Set Default File opening behavior

This is a worthwhile solution to fix this Excel online workbook issue because it helps you open the file with a different method. See the steps below to apply it on your PC.

1. Go to the document library page where the Excel files are stored.

Expert tip:

3. Under the Settings group, select Library settings.

4. Go to the Document Library settings page.

5. Choose Advanced settings.

6. Under Advanced settings, find the Opening document in the browser.

Note: If you cannot find the Library tab or it is grayed out, ask your admin to check the library permission. To do this:

5. Download file and re-save to drive

If Excel Online is not working or not opening files, download the file and try to open it using the Excel desktop application.

If this helps, save the file again, upload it to OneDrive and see if it helps fix the problem.

You can also ensure that you close all documents as you finish using them to avoid such issues and ask anyone you share the documents with to do the same.

6. Check the size of your file

Working with a large file is a potential complication on Excel Online as it may not be supported. Also, it will take too long to open, which may cause Excel Online not to work or to open files.

Usually, five to ten seconds in, your file will fully open or calculate in Excel when you download it, which may be too complex to handle. This is why you see prompts like please wait, we are still loading the workbook or couldn’t open the workbook while trying to use Excel Online.

You’d have to make the file smaller or less complex to make it work. Depending on your calculation needs, you can get the file to open by changing the calculation method to manual.

7. Try a different office suite

If you are still Excel Online is still not working on Sharepoint due to some issues with MS office after trying the fixes above, we’d recommend you give WPS Office a try. It is a great free alternative to Excel Online.

WPS Office allows you to synchronize your documents at any moment, and any change you make on your devices will be applied automatically.

Moreover, WPS Office is suitable for Windows, Mac, Android, Linux, and IOs. You can perform any editing and open all types of Word, Excel, and PDF documents, all in just one place.

⇒ Get WPS Office

Why am I seeing the sorry, we can’t open your workbook in Excel Online because it exceeds the 50MB file size prompt?

This error occurs because the workbook you are trying to open exceeds the file size limit in Office 365 subscription. To open the file, you need to reduce the workbook’s file size.

Also, you can open the file on the Excel Desktop client without reducing it. But the best option for ease of access is to reduce the size.

The Excel for the web not working issue can be annoying, as it can reduce your productivity considerably. Fortunately, it is not the most difficult issue to fix, as shown in this article.

Are you facing other issues like Excel cannot add new cells? Check our extensive guide on the topic to solve the problem for good.

Was this page helpful?

x

How To Make A Histogram In Excel

A histogram is a type of chart you can generate from data in Excel. It makes it easy to summarize the frequency of particular values in your dataset. Excel makes it simple to create a histogram, assuming that a histogram is actually what you need!

What Is a Histogram?

A histogram is a type of chart that uses vertical bars to summarize ranges of data. While it may look like a bar chart, there are significant differences. Bar charts show the differences among variables, whereas histograms are generally used to show the differences among variables in terms of another variable.

Table of Contents

To illustrate, a histogram may be used to show us how common ranges of IQ scores are. Each bar represents a “bin” or range of scores. So something like 0-10,11-20, etc. 

The vertical Y-axis shows us how many measurements of that variable fall within each bin range. So if you have 100 people write an IQ test, every person whose score falls within a particular bin is counted towards the frequency score of that bin.

With a bar chart, you might want to compare something like average IQ scores between countries. In this case, each bar might represent a country and the vertical Y-axis would represent the average IQ of that country.

When Should You Use a Histogram?

HIstograms are a visualization of frequency distribution. It can help you see, at a glance, what sort of distribution your data has. For example, the “Normal Distribution” has the distinctive bell-curve look. A bimodal distribution will have two bumps. You can also see if score frequencies are skewed one way or another. 

Of course, if you really want to determine whether your frequency distribution is normal or not, you’d run a normality test in Excel on your data. Those tests still use histograms as a basis though and creating and observing a histogram is a crucial first step in showing you roughly what sort of distribution you may be dealing with.

What You Need To Make a Histogram

In order to make a histogram, you need a few things:

A set of measurements for a single variable.

Defined “bins” of value ranges.

The first requirement is fairly straightforward. For example, if you have the weights of a group of people, you’d have each measured weight recorded in your dataset. Be careful not to mix the data from groups you don’t want to measure together into one histogram. For example, if you only wanted to look at the weight distribution of a certain age group or gender, you should only include data for that group.

If you wanted to compare the frequency distributions between two groups on a single variable, you’d need multiple histograms. One for each population group.

All About Bins

The next requirement is the trickiest. You need to decide on the “bins” that your frequency counts will be sorted into. The problem is that these may be arbitrary. If you’re going to look at the frequency of scores between 0 and 100, you could have 100 bins, one for each possible score. However, that means 100 bars in your histogram. 

That’s a finely-grained distribution, but it’s probably not all that useful. In the case of test scores, you’re in luck since there are already “bins” in the form of grade symbols. So you could arrange your bins to coincide with those. However, for other types of data you have to invent the bin ranges.

Spend some time considering how you’d like to divide scores into bins and whether the histogram will paint the picture you’re looking for if you decide on a particular “bin width”. 

You can also choose to leave it to an automatic function in Excel, where it will try to decide on a bin width that’s best suited to your data. In Excel, you can also specify the number of bins, which includes optional so-called overflow- and underflow- bins. These capture all scores over and under a specified value.

Creating a Histogram in Excel: Step-by-Step Create the Histogram

Assuming you’ve entered all the values for your dataset, select all the values that should be included in the histogram.

Next, switch to the Insert tab.

Now, under the chart section, select on the picture that looks like a histogram/bar chart. 

From the popup menu, select histogram.

Customize the Horizontal Axis

Now your histogram is in the sheet, but it probably doesn’t look the way you want it to. So next, we’re going to customize the horizontal axis:

The format axis pane will now be open. There are a number of important options here that you can use to tune your histogram so that it looks exactly like you need it to.

Under Axis Options, you can customize the bins we discussed earlier. The two most important settings here are bin width and the number of bins. These options are mutually exclusive. If you specify a bin width in numbers, the number of bins will change automatically and vice versa. You can choose to activate overflow and underflow bins here as well.

From Hysteriagram to Histogram

Hopefully you can now make a histogram easily, but if you need to review basic Excel concepts, try reading Microsoft Excel Basics Tutorial – Learning How to Use Excel

How To Multiply A Column By A Number In Excel (2 Easy Ways)

Although Excel is a powerful data analysis tool, many users use it for basic arithmetic operations.

One common requirement is to multiply an entire column by a number (a constant value).

In this tutorial, I will show you two easy ways to multiple an entire column with a given number.

So let’s get to it!

Suppose I have a dataset as shown below, where I have the Sales Rep names in Column A and their current sales values in Column B.

I want to calculate their Sales target for the next year, which would be 10% higher than their current sales.

This essentially means that I want to increase all the values in column B by 10% (i.e., multiply these values by 1.1 or 110%)

Below is the formula to multiply 110% with the values in the entire column B2 (use this formula in cell C2):

=B2*110%

The above formula would give you the result of the multiplication of value in cells B2 with 110%.

But we also want to get the result when all the values in column B are multiplied with the same number.

To do this, select cell C2, place the cursor at the bottom right part of the selection, hold the left mouse key and drag down. This will copy the same formula for all the cells in column C.

Alternatively, you can also copy cell C2 and paste it into the cells below it (a simple Control +C and Control + V would work).

Note that the result of the formula is dynamic. So if you change the values in column B, the result would accordingly update. If you don’t want this to be dynamic, and instead want static values, you can convert the formula to values.

In the above example, I hardcoded the value 110% in the formula.

Another option is to have the value, with which I want to multiply the entire column, in a separate cell, and use the cell reference instead of hardcoding the actual value in the formula.

The benefit of this method is that in case I change the value in the cell, the formulas would automatically update.

Below I have the same dataset, and I have the new sales target percentage in cell E2.

Below is the formula that will give me the new sales target:

=B2*$E$2

To multiple the entire column, you need to copy the cell with the formula and paste it into all the cells in the column. This will copy the formula as well and give you the right result.

How does this work?

The trick in this method is in using the dollar signs in the reference of the cell that contains the number with which we want to multiply the entire column ($E$2 in this example).

When you add a dollar sign before the row number and the column alphabet, it makes sure that when that formula is copied in other cells, the reference does not change.

In our formula, the $E$2 portion of the formula would not change, while the A2 would become A3 when the formula is copied in cell C3 and it would become A4 when the formula is copied in cell C4, as so on.

Note: In case you’re using Excel for Microsoft 365, where you have access to dynamic arrays, you can simply use the formula =B2:B13*E2. You don’t need to copy for the entire column, the formula itself would spill the result for the entire range.

Another method that you can use to quickly multiply an entire column with a given number is by using the Paste Special technique.

Suppose you have a data set as shown below, where I want to multiply the number in cell E2 with the entire data set in column A.

 Below are the steps to do this:

Copy all the values in column B and paste it in column C. We are doing this as the Paste Special multiplication would be applied in column C, and we would also retain the original values in column B.

Select all the cell in column A with which you want to multiply the number

In the Paste Special dilaog box that opens, select the Multiply option in Operations

That’s it!

The above steps would instantly change all the values in column A and give you the result after it has multiplied these numbers with the value in cell E2.

The result you get is static values (as compared to a formula that you get in the method before that)

Once you are done with the multiplication, you can delete the value in cell E2 (if you want).

One important thing to note about using this method is that when you multiply an entire column using the paste special method, it also copies the formatting from cell E2. So if you give a cell color to cell E2 and use this method, all the cells in column A would also have that color copied to it. To avoid this, you can also select the Value option in the Paste Special dialog box in step 4

So these are two simple methods that you can use to multiply an entire column with a number in Excel.

I hope you found this tutorial useful.

Other Excel tutorials you may also find helpful:

How To Check If Hyperlink Exists In A Worksheet In Excel?

If we have a huge data where hyperlinks have been scattered throughout the worksheet, then it becomes a tedious task to find the hyperlinks and external references. This tutorial will help a user to find the hyperlinks available in a worksheet in the following scenarios −

Find all the hyperlinks in Excel

Find all the hyperlinks linked to a specific text

Find all hyperlink locations with VBA Code

Find All Hyperlinks in Excel

Step 1 − A sample worksheet has been shown below with scattered hyperlinks.

Step 2 − Please note that, using Find and Replace feature, all hyperlinks can be identified easily. For this, press Ctrl+H on the keyboard. The find and replace dialog box will open.

Or you can open the same by following the below path −

Step 6 − It will display all the hyperlinked cells in a list as shown below. You may select each cell individually or hold the control key to select multiple cells from the list.

Find All Hyperlinks Linked to SpecificText in Excel

Step 1 − A sample worksheet has been shown with some similar hyperlinked cell values.

Step 2 − Repeat the step 2 and 3 from the above method.

Find all hyperlink locations with VBA Code

Step 1 − Press Alt+F11 keys on the keyboard and this will open the window of Microsoft Visual Basic for applications.

Step 3 − Now paste the following code in the workspace as following −

Code Snippet

Sub HyperlinkCells() A VBA function used to jump to a location of a worksheet where hyperlinks are available Dim xAdd As String Adding a variable xAdd as string type Dim xTxt As String Adding a variable as xText as string type Dim xCell As Range Adding a variable xCell as Range type Dim xRg As Range Adding a variable as xRg as range type. On Error Resume Next when a run-time error occurs, go to the statement immediately following the statement where the error occurred and execute next. xTxt = ActiveWindow.RangeSelection.AddressLocal Returns a Range object that represents the selected cells on the worksheet in the active window Set xRg = Application.InputBox("Please select range:", "Kutools for Excel", xTxt, , , , , 8) A popup message box to display range of cells If xRg Is Nothing Then Exit Sub If no hyperlink is found then exit from the sub statement For Each xCell In xRg Condition For each xCell variable in the range Next MsgBox "Hyperlink existing in the following cells: " & vbCrLf & vbCrLf & Left(xAdd, Len(xAdd) - 1), vbInformation, "Kutools for Excel" Display a msg box with all hyperlinked cell addresses.) End If end if condition End Sub end sub statement

Step 4 − Now, press F5 to run the code. A dialog box will open as Kutools for Excel.

Step 6 − Then a dialog box will open displaying the cell locations having hyperlinks.

Conclusion

Hence in this article we have learned three methods to find hyperlinks in an excel sheet. Finding hyperlinks in a huge data is a tedious task. Sometimes we need to consolidate the same for reference purpose or data sharing. The VBA code method will actually locate all the hidden hyperlinks also placed without any specific formatting.

Hope this article helped you to learn a new technique of excel. Keep exploring and keep learning.

How To Use Excel Rand Function?

RAND Function (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

RAND in Excel

Rand function in Excel generates random numbers. As the function’s name is RAND, which is used short for Random value, the RAND function generates values between 0 and 1 only. The important thing about the RAND function is the generated value by this function always generates a unique value that is never repeated in the same iterations.

Below is the RAND Formula:

This function does not include any of the arguments; it assumes numbers greater than or equal to zero but less than 1. Unlike the RANDOMBETWEEN function, we need not specify the bottom value and top value of the RAND function. The range of these numbers varies between 0 and 0.99999999…

As an analyst and Excel trainer, it is always a handy function to generate some quick numbers to teach my students. However, it is not the most used formula in corporate offices because the need for RAND function may not arise at all for somebody in the office.

How to Use the RAND Function in Excel?

This function is very simple and easy to use. Let us now see how to use the RAND Function with the help of some examples.

You can download this RAND Function in Excel Template here – RAND Function in Excel Template

Example #1

As a trainer to teach the SUM function to my students, I need to generate some numbers from 0 to less than 1. I quickly do that with the RAND function.

Here I have generated numbers from A2 to E17 and applied the SUM formula for each column in row 18. SUM is taking the range as A2:A17, B2:B17, and so on until E2:E17.

The Answer I got is mentioned in the below image.

Example #2

Assume you are running a lottery shop, and you need to assign some of the numbers against each lottery ticket and select only the numbers greater than 0.95.

Now, we need to generate numbers and chose the winner. Let us go ahead and apply the RAND function to generate numbers.

Note: Since RAND is a volatile function, once you generate the numbers, copy and paste only values using the paste special method.

Now we have generated random numbers in column C.

The winner will be declared if the RAND number is bigger than 0.95.

Now, again we need to apply one more formula to get this done and declare the winner. We need to use the IF condition to declare the winner.

IF condition done here is testing whether the values in column C are greater than or equal to zero and if that logical test is true.

Then it will return the result as Winner. In the above table, the winner is Rihu. Since her score is greater than 0.95, so the formula declared her as the Winner.

If the logical result is false

Then it will return the result as Better Luck Next Time.

Example #3

Using this function, we can generate numbers that are less than 1. What if we want to generate an integer number less than 100? In order to generate random numbers from 10 to 100, we need to use the function INT to get the numbers that are full numbers, i.e. no decimals in place.

Here INT function eliminates the decimal places, and our highest value is 100, and our lowest value is 10.

Things to Remember

It is one of the many volatile functions in Excel. It keeps varying if there are any cell value changes within the Excel worksheet.

Since it is a volatile function, it may reduce the speed of the workbook.

Turn off auto calculation in Excel to avoid volatile changes in the formula.

It is a useful formula to use when you want to generate random percentages from 0% to 100%.

Use the key to change the RAND from formula to value. Edit the formula and press F9.

You need to do the paste special option to eliminate the volatility of the function. Copy the data and paste it as values only.

If you want to generate the two random, numbers use the RANDOMBETWEEN formula, which requires both the bottom value and the highest value in a single formula.

Recommended Articles

This has been a guide to RAND Function. Here we discuss the RAND Formula and how to use RAND Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

Update the detailed information about How To Repair A Corrupted Excel Workbook on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!