Excel for advanced: Conditional formatting, features and other tips

Working with data 

Before we dive into Excel functions and conditional formatting, let's review the basic data handling Excel allows. First up is the ability to import and export data. You can import data from a variety of sources such as text files, databases, web pages, etc. You can then export it as a CSV, PDF or text file. Click on File and select Save As. 

You can also filter, sort and remove duplicates from the data: 

• Filtering will help you view only data that meets certain criteria. How to do it? Select the cells to which you want to apply a filter, and then click the Filter button on the Data tab. You can also create custom filters. 
• Sort: Sorting allows you to sort the data in the table by the selected column. Click the column header you want to sort by, and then use the up and down arrow buttons to sort ascending or descending. You can also find them in the Data tab. 
• Removing duplicates: You can remove data that has duplicate values. Select a range of cells, click the Data tab, and click Remove Duplicates.  

Conditional formatting  

Use conditional formatting when you want to highlight data according to certain conditions. You can use this to identify trends, anomalies, or important information. How to do it? 

1. Select a range of cells. Start by selecting the range of cells to which you want to apply conditional formatting. You can select specific cells or the entire range in the table. 
2. Select conditional formatting. After selecting the cells, go to the Home tab and click Conditional Formatting in the Style section. Clicking this option will display several preset options, as well as the New Rule option. With this option, you can set the type of condition to suit your needs.

1. Set the condition. Select one of the condition types and follow the instructions. For example, if you use Highlight Cells with this content, you can choose to highlight cells that contain specific text, a number, or a date. 
2. Set the formatting. Once you set the condition, select how you want to highlight cells that meet it. You can select a background color, text color, border, and other formatting. This determines how the highlighted cells will look. Finally, confirm the rule.

Most used excel functions 

Excel provides many features that can save you time and make complex calculations easier. You can find all the functions in Excel in the Formulas tab. Let's introduce the most used ones:

• SUM() - summation: formula =SUM() helps you to sum values in a certain range of cells. For example, if you want to sum the values in cells A1 to A7, you would use the formula =SUM(A1:A7). You can also select a range manually using the mouse. 
• AVERAGE() - average: Just select the appropriate cells. 
• MAX() and MIN() - highest and lowest value: The =MIN(A1:A7) formula will give you the lowest value in the range A1 to A7. 
• IF() - conditional calculation: This formula consists of three parts: 
  • Conditions. 
  • What should happen if the condition is met. 
  • What should happen if the condition is not met. 

Example: Formula =IF(A1>10, "Greater than 10", "Less than 10") checks if the value in cell A1 is greater than 10. 

• VLOOKUP() - value lookup: The =SLOOKUP() formula allows you to lookup a value in a table by row, so this function lookups in a vertically oriented table. For a horizontally oriented table, use =VYHLEDAT().  

Creating charts 

You can visualize the data using graphs. Excel offers many types of charts, including bar, line, pie, and more. In the next few lines, you will learn how to create a chart in Excel step by step: 

1. Select the data you want to display in the chart.  
2. Select the chart type from the Insert tab in the Charts section. 
3. Excel creates the chart and displays it in the document. 
4. Right-click on the chart and select Select Data. A bar will appear on the right where you can edit the data itself and its format. 
5. You can add X and Y axis labels, change the name of the graph, and indicate minimum and maximum values. You can also edit the colors, line style, and more. 
6. Finally, you can save or export the chart.  

You can even create a Gantt chart in Excel to help you plan a project or track the timing of tasks. A Gantt chart displays the task name, start date, and end date, and you can use predefined templates that are free from Microsoft Excel to work with it.