If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. MS Excel provides a handy solution to this problem with freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.

Using Freeze Panes

Follow the steps mentioned below to freeze panes.

• Select the First row or First Column or the row Below, which you want to freeze, or Column right to area, which you want to freeze.
• Choose View Tab » Freeze Panes.
• Select the suitable option −
o Freeze Panes − To freeze area of cells.
o Freeze Top Row − To freeze first row of worksheet.
o Freeze First Column − To freeze first Column of worksheet.

Freeze Panes and Conditional Format in Excel

• If you have selected Freeze top row you can see the first row appears at the top, after scrolling also. See the below screen-shot.

Unfreeze Panes

To unfreeze Panes, choose View Tab » Unfreeze Panes.

Conditional Format in Excel

Conditional Formatting

MS Excel Conditional Formatting feature enables you to format a range of values so that the values outside certain limits, are automatically formatted.
Choose Home Tab » Style group » Conditional Formatting dropdown.

Various Conditional Formatting Options

• Highlight Cells Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text, or dates, or that have values greater or less than a particular value, or that fall within a certain ranges of values.
Suppose you want to find cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To.

After Clicking ok, the cells with value zero are marked as red.

Freeze Panes and Conditional Format in Excel

• Top/Bottom Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the top and bottom values, percentages, and above and below average values in the cell selection.

Suppose you want to highlight the top 10% rows you can do this with these Top/Bottom rules.

• Data Bars − It opens a palette with different color data bars that you can apply to the cell selection to indicate their values relative to each other by clicking the data bar thumbnail.

With this conditional Formatting data Bars will appear in each cell.

• Color Scales − It opens a palette with different three- and two-colored scales that you can apply to the cell selection to indicate their values relative to each other by clicking the color scale thumbnail.

See the below screenshot with Color Scales, conditional formatting applied.

• Icon Sets − It opens a palette with different sets of icons that you can apply to the cell selection to indicate their values relative to each other by clicking the icon set.

See the below screenshot with Icon Sets conditional formatting applied.

• New Rule − It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection.

• Clear Rules − It opens a continuation menu, where you can remove the conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the Entire Sheet option, or for just the current data table by clicking the This Table option.

• Manage Rules − It opens the Conditional Formatting Rules Manager dialog box, where you edit and delete particular rules as well as adjust their rule precedence by moving them up or down in the Rules list box.

So, this brings us to the end of blog. This Tecklearn ‘Freeze Panes and Conditional Format in Excel’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft Excel. If you wish to learn Excel and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI

• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources

Power BI Workflow

• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships

Power Query for Data Transformation

• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations

Filters and Drill Down Report

• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report

Power Pivot for Data Modelling

• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions

Data Analysis Expression – DAX Queries

• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features

Power BI Desktop & Administration

• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters

Introduction to Power Q & A

• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

560