Month: May 2021

Freeze top row or first column

Excel’s functionality includes the first row and first column freezing as a default in the “Freeze Panes” dropdown menu. To access it, you need to go to the spreadsheet, click the “View” menu, navigate to “Freeze Panes,” and click the arrow.

You’ll see three choices. The bottom two are referring to the “First Row” and “First Column.” You can choose one, the other, or both.

Fix rows and columns

There might be cases where freezing the first row and column isn’t enough. To ensure convenience, you can also suspend more than one of them. To freeze more than one row, follow the steps below:

– Click the row below what you want visible at a scroll

– Go to View > Freeze Panes

– Choose the top option

The same trick works for the columns:

– Click a column to the right of what you want frozen

– Go to View > Freeze Panes

– Click the unspecified panes option on top of the list

You can also halt columns and rows at the same time. Just click a tile, and go to the “Freeze Panes” selection mentioned above. Once you click, all the rows above and columns to the left of the selected tile will stay in place.

Open the Spreadsheet

First off, you will need to open the spreadsheet that you need to work on. You can do this by double-clicking on the file on your computer, or by opening the file directly from Excel.

Unprotect Sheet

Protected worksheets display a padlock icon next to the tab name in the bottom control panel of Excel. Right-click on the sheet with the padlock to open the context menu with further options. Similarly, if certain cells or ranges of data have been locked they will also show a padlock when hovering over the cells.

In the context menu that appears in the top ribbon of Excel, click on ‘Unprotect Sheet’. If no password was set the file will open automatically. If there was a password set for added protection, move to the next step.

Insert Password

If the worksheet is protected with a password, a window will pop up asking you to input the password. Type in the password and click on ‘Ok’. If the password is correct, the protected worksheet will open.

Be aware that users can protect whole worksheets or even just certain cells or ranges of data within a spreadsheet, but the method of unlocking remains the same.

Upload File

Open your Google Drive on your computer. You can navigate to Google Drive from the grid icon in Gmail, or by typing drive.google.com in your search bar. Next, you need to upload:

– In the top-left of the screen click on the + icon and choose ‘File Upload’ from the dropdown list.

– Select the Excel file you want to upload from your computer.

Open the Excel File

– Double-click on the Excel file in your Drive. This will only open a preview of the Excel spreadsheet. – Click on ‘Open with Google Sheets’ at the top of the screen.

Save as Google Sheets

Now that your Excel file is open in Google Sheets, you can save it as a Sheets file. 

– Click on ‘File’ in the top menu.

• Scroll down and choose ‘Save as Google Sheets’.

Access the Formula Wizard

When you are in your spreadsheet, click on the cell you want to enter the formula into and type =. Formulas always start with a =. Next click on the formula icon in the top bar represented by the fx symbol.

Choose the Formula

The next window will show the available functions. You can choose your formula from the list if you already know what it is, or you can type the description into the box and see which formula Excel recommends.

Insert Function Arguments

You will be taken to a new window, sometimes called the formula palette, where you have to input the data from your spreadsheet. The wizard tells you what information needs to be inserted. 

– Next to the argument click on the grid icon with the red arrow to be taken to your spreadsheet.

• Select the data from the cells in your spreadsheet.

The formula palette will show you a description of the formula in the window, as well as an example of the formula and the result at the bottom so that you can check your information easily.

On Windows

Updating Excel is super easy and can be accomplished in a few clicks. Here are the steps you can take:

– Open the program

– Go to the “File” menu

– Indicate if updates are available

– Click “Update Options”

– Select “Update Now”

– Wait until they install

On Mac

If you own a MacOS device, the process is as follows:

– Open Excel

– Go to Help

– Select the “Check for Updates” option

– Click the “Automatically Download and Install” choice

– Select the “Check for Updates” to initiate the download

• Wait until the new version settles, and the program restarts

Good-old licensed software

Buying the licensed software has been the only way to go for decades. If you’re one of those who still prefer owning your programs and not worrying about subscriptions, this option is for you. An upfront fee, a couple of clicks in the “Installation Wizard,” and the program will be on your PC or Mac, serving you forever.

Microsoft still issues updates to the software.

Subscribe to Microsoft 365

If you’re internet-bound and like your software on all your devices and web, subscribing to the Microsoft 365 program is a better solution. While you only get the program on one computer in the first method, the subscription gains you access to your documents in One Drive storage and other Office programs like Outlook, Word, and PowerPoint.

It also offers Personal, Family, and Business plans, which have various functions and differ in price.

Use a free trial

If you’re not sure about the subscription offer or just want to see how the bundle works for your situation, use the free trial of the app bundle. You can access it from Microsoft’s official website.

Enter Data Into the Spreadsheet

Before you can start to make a graph, you first need to have data available. In your Excel spreadsheet add the data that you want to create the graph for. Also, make sure to add clear headings to your columns and rows as this will be included in your graph’s axis and is needed to clearly understand what you are looking at.

Select the Range of Data to Be Used in the Graph

Next, you need to highlight the data that you want to use in your graph. You can do this by clicking and dragging the mouse across the cells you want to use. Remember to include the headings.

Choose Chart Type

With the data selected click on ‘Insert’ in the top ribbon, now navigate to the dropdown list of all the ‘Recommended Charts’ and choose the graph that will best represent your data from the various options. Once you’ve clicked on the chart type, the program will automatically generate your graph from the data you selected.

Check and Improve Your Graph

Now that the graph has been generated you can check that the elements appear correct, and make adjustments such as the size of the graph, title of the graph and axis, change the colors used, and the display.

Use Conditional Formatting

Conditional formatting is a useful function that can highlight variances in your data so that they become immediately visible. To use conditional formatting you highlight the cells that you want to apply the formatting to, and then choose conditional formatting from the options in the ribbon. A window will open where you can set your criteria for the formatting.

Paste Special

Copy and pasting are used often in Excel spreadsheets, but there are many different ways you can paste data. You can choose to just paste the text, the text and formulas, or even include the formatting. To choose the special formatting that you require, right-click on the cell where you will be pasting the data. Now, click on ‘Special Paste’ and there will be many options to choose from.

Adding Multiple Rows

Sometimes you will get to a point where you need to add more rows to your spreadsheet. This is easy enough to do. Highlight an empty row at the bottom of the sheet where you want to add more, right-click and choose ‘Add Row’. If you want to add multiple rows, highlight a few rows and repeat the process.

Absolute References

This is an indispensable tool when using Excel. Sometimes when you copy a formula it adjusts the formula to the next row or column, but you may not always want that. You may want a value in your formula to always refer to a certain cell or value. To do this you have to place a $ before the row reference and again before the column reference, for example, $A$10. This will lock the reference in the formula.