12 Basic Excel Formulas & Functions To Supercharge Your Reporting Skills

Zveřejněno dne - Poslední aktualizace dne

Understanding Excel helps individuals complete reports on time with minimal struggle. This program comes with various basic functions and formulas that every user should master. Competence in Excel involves knowing the ideal methods to make things work, and what to avoid.   

Entrepreneurs looking for skilled Excel experts can find them on a reputable freelancing site.

While many beginners perceive Excel to be a difficult program, it can be quite simple once the basics are understood. Many offices use Excel to analyze and store data, and companies can save a lot of resources and time by training their staff on the Excel program. Different companies have had to outsource their analytical data report requirements, but the more employees interact with Excel, the more experienced they will become. This lets them learn, and discover new methods of completing work at speed. On top of this, they become a valuable asset to the company.

Using password protection

Protecting an Excel sheet with a password can either prevent third parties from opening it, or enable them to open it but disallow them from making any modifications.

  • How to go about it

Open the ‘Save As’ dialogue box by either clicking on the File tab, or pressing F12. Navigate through to the “Tools” drop down menu, and click on “General Options”. Choose the ideal option between “Password to modify” and “Password to open.” Type in the password and click ok. Before the dialogue box closes, Excel prompts the user to confirm their preferred password. Now click save to store the sheet as password protected. To overwrite the previous unprotected file, one will be prompted with a “Confirm Save” pop-up command.

Filter

The filter function lets you remove or add rows or columns that have specific data. For instance, marketers who record their call data on Excel can use filtering to sort out phone calls made to particular clients. To achieve this:

  • Click on a random cell

  • Navigate to the Data option and click Filter

  • Arrows will appear on the column topics

  • Click a random arrow

  • Choose “Select All”. This clears the check boxes. Now click on your preference and click ok. This will only display a particular type of data.

Converting Excel files to PDF

Individuals using current Excel versions can use the Export option to save sheets as a PDF.

  • Click the file option and select Export.

  • Click create XPS/PDF Document. This opens the Publish as XPS or PDF dialog box.

  • Click the Save As Type option to select PDF

  • Add a name on the file name option.

For Excel 2010 users:

  • Choose Save As from the file menu

  • Choose PDF option from the Save As Type menu

  • On the File name, insert an appropriate name for the file

  • From the dialog box, you can optimize or open the PDF document after publishing. Click the Options menu to pick any part of the Excel document that you need to publish.

  • Click Save to store the document.

For Excel 2007 users:

Excel 2007 users will have to download and install an Excel compatible add-in program to facilitate the process. Once done:

  • Click the Save As option

  • Choose XPS or PDF option. This opens the Publish as XPS or PDF dialog box

  • Type the name by which to save the file. Select PDF as the file type then click publish

Converting a PDF document back to Excel is a complex process, but you can still access the original Excel spreadsheet as the conversion does not delete it from the computer.

Conditional Formatting

Conditional Formatting enables you to include illustrations in spreadsheets, depending on the data. For instance, you can have different columns in different colors. Access the conditional formatting option by clicking on the Home menu. 

Date

 The DATE function in Excel plays an important role. Classifying dates by month can be a complex task, but you can create a month column followed by the date column with the following formula.

=DATE(YEAR(b2),MONTH(b2),1)

In this case, B2 is the cell where the date will be. When the date in that cell is similar to 2017-08-13, the month cell will be 2017-08-01. The month shows the first day of every month no matter the date. This makes it easier to classify or filter the pivot table according to the month. When this formula is on the first cell, you can double click at the rear right hand edge of the cell to copy it on all cells. This is convenient as users do not have to write the formula one by one.

Format Painter

The Format Painter is an easy function. It enables users to copy only the formatting from a specific spreadsheet. Individuals can paste it on a new series of cells. 

HLookup & Lookup

This formula is exciting to use. It enables users to pick data from a different spreadsheet through the use of a universal identifier. For instance, a ticketing agent has stored their ticket data for the month of August in one sheet. The data includes the name of the ticketing agent who made the sales. Let's say the agent wants to arrange the tickets according to the various groups they work in. Open a new sheet, set up an easy sheet comprising of two columns. They should have the agent’s name and their groups. Navigate back to the original spreadsheet and set up a ‘group’ column. Use VLOOKUP as it is vertical. When data appears horizontally, you use HLOOKUP. The former option finds the agent’s name, and corresponds it to the group name on the new spreadsheet. The formula may be similar to this: =VLOOKUP (|B2,’OtherSheet’!$B$2:$C$26,3,FALSE). The dollar signs here play an important role.  

Dollar sign

It is important to understand how the dollar sign functions in Excel. The program is capable of modifying a formula in a cell in correspondence with the row it is in. When individuals want to multiply cells in a specific column by the value in a different individual cell, the dollar sign ensures that relativity is inactive. This prevents the cell number from changing through the formula. Therefore, the formula shall be similar to:

=B3*$E$18

Data Validation

Data Validation enables you to set up various options such as a drop down list. Imagine having to choose between a “no” and “yes” in a particular column, or any of the various options available. A declining list enables you to conveniently choose your preferred options. It also lts you format the spreadsheets consistently. 

IF and IFS

Include IFS or IF after Average, Sum, and Count, in order to include a sequence in the formulas. The S lets you link collective sequences on the formula. For instance, if you have a table of points and you want to ascertain how many foolproof 100% points a specific group got, the formula will be similar to this: =COUNTIF(B2:B500,”100”) 

Sum, Average, Count

Remembering these functions is simple and easy. You do not have to keep on looking for the function option. Include an equal(=) sign prior to adding, counting, or finding the average of any chosen series of cells. These functions play a vital role in determining the subsequent tip.

IF Statements

IF statements enable users to administer logic in specific cells. For instance, if you have a tabulation of all your phone calls displayed in a column, complete with the number of seconds every caller waited for someone to answer their calls, you can find the number of calls answered within 60 seconds. The formula to use will be similar to this

=IF(B2<=60,Yes”,”No”)

“Yes” represents calls answered within or in less than 60 seconds. “No” represents those whose calls took more than 60 seconds.  

Finally 

Excel is an exciting program that makes work easier. Regular use of Excel gives you the experience and knowledge to make a real difference in your workplace.

These 12 basic functions and formulas are easy to use. Regular practice will let you grasp them with minimal struggle.You can access Excel both from a PC and Google Sheets - and remember, the above functions are just a few examples of what you can do with this program.

What are your favorite Excel functions and formulas? We would love to hear about them in the comments, so please feel free to leave us your feedback. And so share on social media!

Zadán 20 září, 2017

LucyKarinsky

Software Developer

Lucy is the Development & Programming Correspondent for Freelancer.com. She is currently based in Sydney.

Další článek

Collecting Design Excel Sheets