Excel for Data Analysts: The Beginner's Survival Guide to Formulas, Shortcuts, and Real Skills in 2026

Nobody ever prepared you for how terrifying using Excel would feel.

You crack open a spreadsheet. Endless rows. Column headings that seem like nonsense. A formula bar that just stares back at you. And inside your brain, the little voice that goes something like “am I really supposed to be able to do all of this?”

Don’t worry, you are not alone. Everybody who has ever started using Excel, from the most skilled analysts to the newest business students to marketers, has thought exactly what you are thinking right now. The only thing that separates the people who become skilled at using Excel and those who will continue feeling confused forever isn’t their intelligence, it's knowing where to start.

That’s why we’ve created this guide. This guide will cover everything you need to learn how to go from knowing next to nothing about Excel formulas to cleaning data, analyzing it and presenting it in a manner that will knock people off their socks.

By the time you reach the conclusion of this blog post, you should know how relevant Excel is even in 2026, what Excel can do for your career, the basic formulas that you must learn, the keyboard shortcuts that can save you many hours every week, and the secrets behind being a good analyst.

Let us dig into this.

Why Excel Still Matters in 2026: More Than Most People Think

Here is something worth knowing before anything else.

A lot of people assume Excel is old technology that Python, Power BI, Tableau, and SQL have made it irrelevant. That assumption is wrong, and it costs beginners valuable time when they skip Excel to chase flashier tools.

The reason why Excel does most of the daily analyses in almost all companies is that it is accessible from every desktop, works well with almost any business application, and can produce outputs within minutes, not weeks. The current Excel is way superior to its predecessor from a decade ago, thanks to dynamic arrays, LAMBDA functions, Power Query, Power Pivot, and integrated Microsoft Copilot AI. It is now a proper analytics software tool that can process tens of millions of rows of data.

Being fluent with Excel is among the topmost sought-after abilities by Nepali businesses when it comes to the fields of finance, marketing, operations, HR, data analytics, and business management. Almost all of them hire new graduates who are familiar with Excel during job interviews, and in job openings, data analyst jobs require Excel along with SQL and Python.

This is why Excel is so important for beginners, precisely because it bridges the gap between no skill and actual ability. There is nothing to download. There is nothing to program. You simply start using the application, understand how the formulas work, and in a week of use, you already have results to show.

Excel mastery proves to employers that you are pragmatic and that you can be productive from day one which is critical when you are a new graduate entering the job market.Explore more about what Nepal's employers are looking for in entry-level data roles in the in-demand tech skills guide on the Knowlary blog.

Part 1: The Benefits of Learning Excel as a Data Analyst

Before jumping into all the tricks and tips, realize what Excel will actually do for your career. These are not theoretical gains, these are practical career perks that will happen to you in real life.

Benefit 1: You Can Get Hired Faster

Nepalese employers ranging from financial technology firms in Kathmandu to banks in Biratnagar to marketing firms in Pokhara require Excel proficiency in interviews even more consistently than just about any other technology. The candidate with recent computer science qualifications who comes in for an interview and can perform pivot tables, XLOOKUP, and data cleaning in Excel instantly separates themselves from candidates that have just learned theory.

Benefit 2: You Understand Data Before You Touch Code

One of the most common errors people tend to make is rushing into learning Python and SQL before they know how to perform data analysis. Excel makes one think about the data structure, data cleaning, aggregation, and visualization. People who use Excel to analyze data before going for Python and SQL develop good instincts that help them become faster.

Benefit 3: Every Industry Uses It

From finance to marketing, from healthcare to logistics, from education to government every industry in Nepal that deals with information uses Excel. Being able to use Excel is not a specialized skill. It is a universally useful skill that increases your value in any profession you choose to pursue.

Benefit 4: It Is the Fastest Tool for Quick Analysis

If a manager wants a fast response, like “how many customers bought over NPR 5,000 worth last month?”, then Excel gives the response in two minutes flat. Python and SQL will require more time to process this type of one-off question. Excel beats all other tools when it comes to speed and ad-hoc analysis.

Benefit 5: It Connects Directly to Advanced Tools

But don’t think that by mastering Excel, your work is done; that’s only the start of your journey. It will make learning SQL, Power BI, Python, and Tableau much simpler since you know the logic behind data handling already. Any data skill that you acquire after Excel will only enhance what you learned from Excel.

If you are serious about building a career in data, the Data Science and Machine Learning course at Knowlary takes you beyond Excel into the deeper data skills Python, ML models, real datasets that are reshaping Nepal's tech job market.

Part 2: The Essential Excel Formulas Every Beginner Must Know

This is the section most beginners need the most. Formulas are the engine of Excel:  they are what turn a spreadsheet full of raw numbers into actual analysis.

Start here. Learn these in order. Do not move to advanced formulas until these feel natural.

Category 1: Basic Math Formulas

1. SUM: Adds up a range of numbers                                                                        =SUM(B2:B10), It is used for total sales, total expenses, total marks, any addition across a range.
2. AVERAGE:  Finds the mean of a range                                                                  =AVERAGE(B2:B10), It is used for average salary, average score, average order value.
3. MIN and MAX:  Find the smallest and largest values in a range                                 =MIN(B2:B10), =MAX(B2:B10). They are used for finding the lowest and highest values in any dataset instantly.
4. COUNT and COUNTA:  Count cells with numbers (COUNT) or any non-empty cells (COUNTA) =COUNT(B2:B10), =COUNTA(A2:A10). They are mainly used for counting how many entries exist in a column.

Category 2: Conditional Formulas

1. IF: Returns one value if a condition is true, another if false                                          =IF(B2>50000, "Above Target", "Below Target"). It is used for flagging performance, categorizing data, and automating decisions.
2. COUNTIF:  Counts cells that meet a specific condition                                      =COUNTIF(C2:C100, "Kathmandu"), It is used for counting how many times a value appears in a column.
3. SUMIF:  Adds values that meet a specific condition                                               =SUMIF(C2:C100, "Kathmandu", D2:D100), It is used for total sales from one city, total expenses in one category.
4. AVERAGEIF:  Averages values that meet a condition                                   =AVERAGEIF(C2:C100, "Marketing", D2:D100), It is used for average salary by department, average score by category.
5. IFS:  Tests multiple conditions without nesting multiple IF formulas                          =IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2<70,"D"). It is used for grading systems, performance tiers, and multi-level categorization.

Category 3: Lookup Formulas (The Most Important Ones)

1. XLOOKUP:  Searches for a value and returns a corresponding result (the modern replacement for VLOOKUP)                                                                                        =XLOOKUP(F2, A2:A100, B2:B100, "Not Found"), It is used for finding a customer's name from their ID, retrieving product prices from a master list, matching records across two datasets.

XLOOKUP is one of the most valuable Excel functions for analysts. It searches in any direction, handles missing values cleanly, and eliminates the column-counting mistakes that made VLOOKUP unreliable for years.

1. VLOOKUP:  Still useful for older Excel versions and legacy files                               =VLOOKUP(F2, A:B, 2, FALSE) It is used for: the same purposes as XLOOKUP when working in older Excel environments.
2. INDEX MATCH:  A powerful combination for flexible lookups in complex datasets =INDEX(B2:B100, MATCH(F2, A2:A100, 0)), It is used for: situations where XLOOKUP is not available or when you need to match across multiple criteria.

Category 4: Text Formulas

1. CONCAT or TEXTJOIN: Combines text from multiple cells                                       =CONCAT(A2, " ", B2), =TEXTJOIN(", ", TRUE, A2:A10). These are used for: combining first and last names, creating full addresses from separate columns.
2. LEFT, RIGHT, MID: Extract specific characters from text                                                          =LEFT(A2, 3): extracts first 3 characters, =RIGHT(A2, 4): extracts last 4  characters, =MID(A2, 3, 5): extracts 5 characters starting from position 3. These all are mainly used for extracting codes, area codes, or identifiers from longer text strings.
3. TRIM:  Removes extra spaces from text                                                                       =TRIM(A2) this is used this for cleaning messy imported data where cells have leading or trailing spaces that break your formulas.
4. UPPER, LOWER, PROPER:  Change text case                                                             =UPPER(A2): ALL CAPS, =LOWER(A2): All lowercase, =PROPER(A2): First Letter Capitalized. They are mainly used for standardizing inconsistent text data before analysis.

Category 5: Date Formulas

1. TODAY and NOW: Return the current date and current date-time                                         =TODAY(), =NOW() these are used  for: calculating how many days since an event, creating reports that update automatically.
2. DATEDIF:  Calculates the difference between two dates                                           =DATEDIF(A2, B2, "D"): difference in days, =DATEDIF(A2, B2, "M"): difference in months, =DATEDIF(A2, B2, "Y"):  difference in years. These all are mainly used for: calculating employee tenure, subscription age, project duration.
3. NETWORKDAYS:  Counts working days between two dates (excludes weekends) =NETWORKDAYS(A2, B2) this is used for: project timeline planning, calculating business days for deadlines.

Category 6: Error Handling

IFERROR: Returns a clean value instead of a formula error                             =IFERROR(XLOOKUP(F2, A2:A100, B2:B100), "Not Found")                                                            Use this for: preventing #N/A, #DIV/0!, or #VALUE! errors from breaking your analysis. Wrap any risky formula in IFERROR so missing matches return a sensible blank or zero rather than errors that propagate into your charts.

Part 3: PivotTables: The Single Most Powerful Feature for Beginners

Regardless of whether you gain any other knowledge from this guide, the one thing that you should take away from this guide is PivotTables.

PivotTables summarize your entire dataset within a few moments, and they do so by grouping, counting, totaling, and calculating averages of your data within any categories you desire.

How to Create a PivotTable (Step by Step)

1. Click anywhere inside your data table
2. Go to Insert → PivotTable
3. Choose where to place it (new worksheet recommended)
4. Click OK
5. In the PivotTable Fields panel on the right, drag fields into:
6. • Rows: what you want to group by (e.g., City, Department, Product)
   • Values: what you want to calculate (e.g., Sales Amount, Count of Orders)
   • Columns: for cross-tabulation (optional)
   • Filters: to filter the entire PivotTable by a category

That is it. In under two minutes, you have a summarized analysis of your entire dataset.

Keyboard Shortcut for PivotTable

Press Alt + N + V on Windows to open the PivotTable dialog instantly.

PivotTable Tips for Beginners

• Always transform your data into an Excel Table (Ctrl + T) before anything else this allows your PivotTable to be updated automatically whenever you add a row
• Click right on the PivotTable to open Refresh  always refresh whenever you update your data
• Utilize PivotCharts (right-click on the PivotTable → PivotChart) to view your summary graphically

Part 4: Data Cleaning: The Skill Nobody Teaches But Everyone Needs

An honest fact about data analysis is that 60 to 80 percent of the actual work of any data analyst is devoted to data cleansing rather than data analysis. Data collected from forms, databases, or exports is often messy duplicates, inconsistent formatting, spaces, blank cells, wrong data types.

There are some great Excel functions for data cleansing. These are the basics you need to know as a beginner.

• Remove Duplicates: Go to Data → Remove Duplicates Select which columns to check for duplicates → Click OK Excel removes all duplicate rows automatically.
• Find and Replace: Ctrl + H opens Find and Replace Use this to fix inconsistent naming, changing "Ktm" and "ktm" and "KTM" all to "Kathmandu" in one step.
• Text to Columns: Go to Data → Text to Columns Use this to split one column into multiple separating full names into first and last name columns, or splitting date-time into separate date and time columns.
• Flash Fill: Begin entering a pattern into a column beside your information. Once you have entered the second piece of information, hit Ctrl+E and let Excel do its magic of completing the rest of the pattern for you. Example: If column A contains “Ram Shrestha” and you enter “Ram” into column B, Flash Fill will extract the first names automatically.
• Filter Blank Cells: Click the filter dropdown on any column header → Uncheck everything except "Blanks" This isolates all rows with missing data so you can decide how to handle them.
• Data Validation: Data → Data Validation

Using this option, you can put restrictions on what can be entered into the cells; for instance, you can ensure that only dates are entered, only positive figures can be entered, or even enter information only through drop-down options.

Part 5: Charts and Visualization: Making Data Tell a Story

Numbers alone do not communicate. Charts do. A great analyst knows not just how to calculate but how to present findings in a way that makes sense to someone who has never seen the data before.

Choosing the Right Chart Type

1. Comparison between categories: Bar chart or Column chart
2. Trend over time: Line chart
3. Part of a whole: Pie chart (maximum 4–5 slices) or Donut chart
4. Relationship between two variables: Scatter plot
5. Distribution of values: Histogram
6. Multiple metrics on a dashboard: Combination chart

Chart Tips for Beginners

• Remember to include a meaningful title that explains what the graph is about instead of "Sales Graph," use "Monthly Sales for Each Region, Q1 2026"
• Eliminate superfluous grid lines, borders, and 3D visuals, as they tend to draw attention away from the numbers
• Use the Conditional Formatting feature (Home tab → Conditional Formatting) to add color to cells according to their values – an overlooked visualizing technique that transforms your data table into a visually appealing one without even creating a graph
• Limit pie graphs to no more than three or four segments anything beyond that, a bar graph conveys the same message effectively

Part 6: Power Query: The Data Analyst's Secret Weapon

The least utilized feature in Excel for newbies is Power Query, which gives you an edge over others when you know about it.

Power Query in Excel allows you to connect Excel to outside sources, including Excel workbooks, exported CSVs, databases, or any information on the web and process it with just one click. The main benefit of using it is that once you configure the data transformation in Power Query, you can simply do that with another click.

How to Access Power Query

Go to Data → Get Data → From File / From Database / From Web

What You Can Do With Power Query

• Automatically combine data from many different Excel files
• Cleanse your data and do transformations using a graphical user interface (no need for formulas)
• Delete columns, split columns, replace values, and convert data types
• Filter rows according to certain criteria
• Join two data sets together (a kind of VLOOKUP for an entire table)
• Re-run all transformations at once with a single click as your source data changes

For anyone new to data analysis and looking to clean up real world data from multiple sources, Power Query makes it possible without having to be technically savvy.

Part 7: Excel Keyboard Shortcuts Every Beginner Must Know

Mastering Excel shortcuts is the fastest way to boost productivity. Analysts who use shortcuts consistently work three times faster than those who rely on mouse clicks  and that speed compounds enormously over weeks and months of daily use.

Here are the most important shortcuts organized by category. Print this section. Keep it next to your screen until these become muscle memory.

1. Navigation Shortcuts

• Ctrl + End: Last used cell
• Ctrl + Home: Go to A1
• Ctrl + Arrow Keys: Jump to data edge
• Ctrl + Shift + Arrow: Select to data edge
• Ctrl + G: Go To cell/range
• Ctrl + F: Find data
• Ctrl + H: Find & Replace
• Selection Shortcuts

1. Ctrl + A: Select all data
2. Ctrl + Shift + End: Select to last used cell
3. Shift + Space: Select entire row
4. Ctrl + Space: Select entire column
5. Ctrl + Shift + L: Toggle filters

Editing Shortcuts

1. Ctrl + C / Ctrl + V: Copy & Paste
2. Ctrl + X: Cut
3. Ctrl + Z: Undo
4. Ctrl + Y: Redo
5. Ctrl + D: Fill Down (copy cell above to selected cells below)
6. Ctrl + R: Fill Right (copy cell to the left across selected cells)
7. Ctrl + ;: Insert today's date
8. F2: Edit cell
9. Esc: Cancel edit without saving changes
10. Delete: Clear cell
11. Alt + E + S + V: Paste Values only(removes formulas, keeps numbers)

Formatting Shortcuts

1. Ctrl + B: Bold
2. Ctrl + I: Italic
3. Ctrl + U: Underline
4. Ctrl + 1: Open Format Cells dialog
5. Ctrl + Shift + $: Apply Currency format
6. Ctrl + Shift + %: Apply Percentage format
7. Alt + H + B: Add borders
8. Alt + H + H: Highlight cell with color

Formula Shortcuts

1. Alt + =: AutoSum: instantly sums the selected cells or the cells above.
2. Ctrl + `: Toggle between displaying formulas and calculated values in the worksheet.
3. F4: Toggle absolute and relative cell references while editing a formula ($A$1 → A$1 → $A1 → A1).
4. Ctrl + Shift + Enter: Enter an array formula in older versions of Excel.
5. Tab: Accept the autocomplete suggestion for a function name while typing a formula.

Workbook and Sheet Shortcuts

1. Ctrl + N: Create a new workbook.
2. Ctrl + S: Save the current workbook.
3. Ctrl + W: Close the current workbook.
4. Ctrl + P: Open the Print dialog.
5. Ctrl + Page Up / Page Down: Switch between worksheet tabs.
6. Shift + F11: Insert a new worksheet.
7. Alt + N + V: Insert a PivotTable.
8. Ctrl + T: Convert the selected data into an Excel Table.

Part 8: Pro Tips That Separate Good Analysts From Great Ones

You now have the formulas, the shortcuts, and the key features. Here are the habits and strategies that will make you genuinely good, not just technically capable.

Tip 1: Always Work With Tables, Not Raw Ranges

Press Ctrl + T at the instant that you start working with any data range to instantly turn it into an Excel Table. You can auto expand your table by adding new rows; use structured references such as Sales[Amount] instead of using $B$2:$B$5000; and automatically feed PivotTables and Power Query instead of manually updating ranges. It solves most broken formula issues for analysts.

Tip 2: Name Your Ranges

Instead of writing =SUM(B2:B100), go to Formulas → Define Name and name that range "MonthlySales". Now write =SUM(MonthlySales). Your formulas become self-documenting and far easier to audit and maintain.

Tip 3: Document Your Work

Every significant workbook should have an accompanying "Notes" sheet. Record the source of information, its date of last update, the functions performed by the main formulas, and your assumptions. Your future self, as well as your colleagues, will be very thankful to you.

Tip 4: Use IFERROR Everywhere

Put IFERROR around all lookups formulas. When a formula can return blank or "Not found" rather than "#N/A," then it is one that won't break your PivotTables and graphs. That is how you differentiate those workbooks that make it through a quarter versus those that crumble when the data changes.

Tip 5: Freeze Panes for Large Datasets

In cases where you are dealing with huge datasets, go to View → Freeze Panes → Freeze Top Row. This is done so that even as you scroll down through thousands of rows, the column names remain visible to you.

Tip 6: Use Conditional Formatting as a Quick Visual Scan

Conditional Formatting (Home → Conditional Formatting → Color Scales or Data Bars) should be applied to the dataset before creating any charts. It makes it possible to see all the patterns and trends in the data right away without creating any charts.

Tip 7: Learn Power Query Early, Not Later

For most newbies, Power Query is considered a complex function that can be learned at a later time. Consider Power Query, a simple function to learn now. Since every analyst works on data gathered from different sources, Power Query will save you many hours of hard work each week.

Tip 8: Practice on Real Data, Not Toy Examples

The best way to build Excel skills is not to follow tutorial examples with fictional data. Download a real dataset from Kaggle or data.gov.np Nepal's open data portal and try to answer a real question about it using Excel. The moment you hit a problem that a tutorial did not prepare you for, you learn ten times faster than in any structured lesson.

Tip 9: Build a Portfolio Dashboard

Make a single dashboard using Excel that utilizes all that you have learned about  data cleaning, functions, PivotTable, graphs, and conditional formatting  to give a full analysis from the data collected to visual interpretation. This single document will impress your employer much more than any certification.

Read more about how to build a portfolio that gets you hired in Nepal; an Excel dashboard is one of the most concrete portfolio pieces a data analyst beginner can create.

Tip 10: Save Early, Save Often

Ctrl + S every five to ten minutes. Enable AutoSave if you are using Microsoft 365. This is not a tip, it is survival.

Excel and Your Career in Nepal: Where This Skill Takes You

Excel expertise is indeed a true career booster for the Nepalese job market, as these professions are quite lucrative.

Data analysts (who make use of Excel along with SQL and Python on a regular basis) get paid NPR 30,000 to NPR 60,000 per month as beginners in Nepal, whereas the professionals at an intermediate level earn NPR 80,000 to NPR 1,50,000 per month.

For students who want to understand exactly where data skills lead from Excel basics all the way to machine learning and AI, the future of AI and data jobs in Nepal is a genuinely important read. And if you are a BCA or BSc CSIT student looking to turn your Excel and data skills into an internship, the guide on landing data science internships for BCA and BSc students in Nepal gives you a practical action plan.

The transition from Excel novice to data analyst is probably the most easily attainable career transition available in Nepal’s technology based economy at present. Mastery of Excel is a real skill rather than just an interim measure.

The Honest Truth About Learning Excel

This is the thing nobody tells you when you start out.

Excel is easy. It is just new to you. Every single formula you feel intimidated about right now will become second nature to you once you have used it for the fifth or sixth time. Every single shortcut that feels weird will become second nature to you in two weeks' time.

The analysts who actually know their way around Excel do not become experts through watching tutorials. They became experts because they opened up Excel and tried things, broke things, found out why it broke, and did all of the same again the following day.

You will be confused. You will get formulas returning results you do not understand. Your PivotTable will not reflect your expectations. You will be making mistakes left, right, and center. Do not think that you are bad at Excel because of this. On the contrary, it means you are learning.

The only thing separating you from someone who actually knows Excel is time spent inside Excel. Do it today. Make something. Break it. Fix it. And come back tomorrow.

Want to build beyond Excel into Python, machine learning, and the full data science stack? Explore Knowlary's Data Science and Machine Learning course built for Nepal's IT aspirants and structured around what real employers test for. And check the Knowlary blog for more career guides, skill-building resources, and honest insights into Nepal's tech job market.

External Resources to Practice and Go Deeper

• Microsoft Excel Official Training: Free official tutorials and documentation straight from Microsoft
• Kaggle: Download free real world datasets to practice Excel analysis on genuine data
• data.gov.np: Nepal's open government data portal; practice with data relevant to Nepal's economy and society
• Google Sheets: 95% of Excel formulas work identically in Google Sheets use it for free if you do not have Microsoft 365
• ExcelJet: The best free Excel formula reference on the internet bookmark this immediately
• GitHub: Share your Excel dashboards and data projects as part of your professional portfolio