Tag Archives: Excel

How to break an Excel Password

How to break an Excel Password

How to break an Excel Password

 

There are many ways to protect an Excel spreadsheet from viewing and editing. One of them is to protect cells of selected ranges of the sheet against editing, changing the formatting, etc. There are situations, for example, that we forget our password to the sheet that we would like to unlock. We may also remember a password that, in fact, is incorrect.

The first thing to do is to check if CapsLock is on or off. The most common mistake in typing an incorrect password is CapsLock turned on. Before trying to crack a password in Excel, I suggest checking the password in both uppercase and lowercase.

  • Right Click on the name of the sheet
  • Click Unprotect Sheet…
  • Enter password what you remember
  • Click CapsLock
  • Enter again the same password

In addition, it may be worth trying to check frequently used passwords, such as 123456 or 654321. If these attempts do not help, you can try to crack the password in the sheet by using one of the three methods I have listed below.

Method 1. Using Macro VBA
Method 2. Unzip file and remove relevant code
Method 3. Using Google Sheet

Method 1. Break password using macro (VBA).
File with extension (.xls) version 2003 or before

    • Open a visual basic editor. Alt + F11
    • Right click on Sheet
    • Select Insert > Module
    • Paste code below  in the General Declaration page
Sub BreakPassword()

   Dim i As Integer, j As Integer, k As Integer 
   Dim l As Integer, m As Integer, n As Integer
   Dim i1 As Integer, i2 As Integer, i3 As Integer
   Dim i4 As Integer, i5 As Integer, i6 As Integer

   On Error Resume Next

   For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
   For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
   For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
   For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

   ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
   Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
   Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

   If ActiveSheet.ProtectContents = False Then
      Exit Sub
   End If

   Next: Next: Next: Next: Next: Next
   Next: Next: Next: Next: Next: Next

End Sub
  • Run the macro, press F5
  • If you received message below your sheet is unprotected.

 

 

 

 

Method 2. Break password removing relevant code. File with extension .xlsx version 2007 or later

  • Open a visual basic editor Alt + F11
  • Find relevant Sheet name
  • Unzip Excel file (e.g. you can use 7-Zip). Right Click on the file  and select from the menu 7-Zip —> Open archive
  • In the archive, go to the xl —> worksheets folder and set on the xml file with the code name you are looking for
  • Press F4 to go to editing in the default text file editor (most often it will be Notepad)
  • Open relevant file
  • Find, Select and delete text (blue selection below) from <sheetProtection…/> to  before<pageMargins…>

  • Save changes

The new file should be with unprotected relevant Sheet.

Method 3. Break password using Google Sheet

You have to be login-In to Google Account.

  • Start a new spreadsheet
  • Select File > Import
  • Select Upload from the menu.

  • Drag relevant file from your device and drop in the box above
  • Browse to the Excel spreadsheet on your computer. Select it, and click Open.
  • In the Import file window, select Replace spreadsheet and select Import Data.
  • Downlead as > Microsoft Excel (xlsx)
  • Choose where to save the document
  • Select Save As, give a name and Save

Now Your Excel Spreadsheet should be unprotected.

Send file: How to crack password in Excel? 

Each of the methods presented may not be sufficient in specific cases. There are different reasons for that. Different types of security are used, which requires additional complex actions. In such cases, we can try unlocking our Excel spreadsheet. Just click the link below and send the file for reviewing.

Request a quote

I also invite you to familiarize yourself with my projects in Excel, available in the Download tab, they may turn out to be useful.

Excel Pace Calculator

Excel Pace Calculator

Excel Kalkulator Biegacza
The Excel Runner Pace Calculator, is available for download at the bottom of the article or in the Downloads tab and it offers the following functionalities:

• After declaring the Event and Distance, it automatically calculates Speed and Pace in kilometres and miles.
• After declaring the Pace and Units, it automatically calculates Speed and Time to cover popular Distances and Marathon laps.
• Converts between Pace and Speed
• Converts between SI and Imperial metrics.
• Dynamic visualisation.
• Pace band.

Definition of Pace and Speed

The Pace is the time, most often given in minutes, that is needed to cover 1 km. Alternatively, we give time to cover 1 mile or to cover a special distance, e.g. a lap.

Pace = Time  / Distance [min/1km] or [min/1mil]

The Speed ​​is the distance of time. In the SI system, we most often give the number of kilometres we manage to cover in one hour [km / h] or the number of meters per second [m / s].

Speed = Distance / Time [km/1h] or [miles/1h]

Convert miles to kilometres:

=CONVERT(1,"mi","km")

In the first argument of the function, instead of 1, we can insert the address of the cell in which the number we want to convert is given.

1 mile = 1.609344 kilometres. In Excel, the formula is used to

Setting the runner’s calculator.

Fill the yellow cells in the red border only. The rest of the spreadsheet is fully automated and write-protected.

In the upper part of the spreadsheet after declaring the Target (it is important to keep the hh: mm: ss format) and Event  (from the drop-down list) the Pace and Speed in kilometres and miles are given.

2nd part of Pace Calculator setting:

  • Pace or time given in the correct format hh: mm: ss.
  • Unit to choose [kilometres] or [miles] from drop-down list.
  • Leap in seconds in the Pace column between rows.

Converts between Pace and Speed.

Knowing the Pace, saved in the hh: mm: ss format in an Excel cell (e.g. C15), we can calculate the Speed using the formula:

=24/(C15*24)/24

Calculating the time needed to overcome various distances based on the declared Pace. If in column C starting from C15, the pace is given in the format hh: mm: ss, and in line 15 starting from G15 the distance to do, then the formula in this case for the cell G15 looks in the following way:

=($C15*24)*G$13/24

The dollar symbol ($) in front of the letter C in cell addressing means that regardless of where you paste the formula, the addressing of column C will remain the same, but if it appears before the number, e.g. 15, the addressing of row 15 will remain unchanged.

The graph on the left shows the time to cover popular Event based on the declared Pace.

At the bottom of the spreadsheet there is a table with the Actual Pace declared in Miles. In the yellow fields we give the real Time achieved in individual sections of the marathon. As a result, we can control how the Plan is implemented in relation to the assumed Pace. In the Difference column, the difference when the implementation of the plan is negative is highlight in red.

Pace Band Worksheet Settings.

Set up your preferences:

  • Pace (hh:mm:ss format) [miles]
  • Strategy (drop down list)
    • Positive Split
    • Even Splitting
    • Negative Split
  • Deviation [%]

Download Excel Runner Pace Calculator by clicking button below:

Download Free

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

IFS vs nested IF

IFS vs. nested IF

Excel IF Statement

Syntax of Excel IF Statement:​

= IF ( Logical Test , Value if True , Value if False )​​

Logical test Examples:

• Equal to: A1=A2.
• Higher than: A1>A2.
• Higher or equal than: A1>=A2.
• Less than: A1<A2.
• Less or equal than: A1<=A2.
• Not equal to: A1<>A2.

If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.

=IF(A1>5,"Pass","Fail")

If cell A1 value is higher then five, formula return Pass otherwise Fail.

In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.

Nesting of the IF function

I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet.
I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.

= IF ( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good,  otherwise Poor.

IFS function alternative

In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.

= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
=IFS(A1>5,"Excellent",A1>3,"Good")

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three,  otherwise return error #NA.

It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.

=IFS(A1>3,"Good",A1>5,"Excellent")

In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA. 

IFS vs nested IF

Benefits of IFS over nested IF:

• The IFS function allows you to easily enter further logical conditions without nesting.
• Due to the lack of nesting, the performance of IFS is much more efficient
• Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only.
• If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.

= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

If the value in cell A1 is higher than five, the formula return Excellent, if not, but higher than three return Good, otherwise it return Poor.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.