Introduction to Excel Basics
Back in Ancient times, humans recorded their information on stone tablets that could only be so big. Today a single spreadsheet in Excel contains 16,384 columns and 1,048,567 rows. That's over 17 billion cells! This article will guide you to the fundamental aspects of excel, helping you get started with navigating, entering and formatting data. Whether you are a student, professional, or just someone looking to improve their data management skills, this is the guide for you.
What is Excel?
Microsoft Excel is a powerful spreadsheet application used for data entry, calculation, and analysis. It is widely used in various fields such as finance, project management, and data analysis to organize and manipulate data efficiently.
Lets start with the Excel window, which can be broken down into 5 sections.
Header —
The header bar contains the quick access toolbar where you can store your favorite icons.
It also contains the file name of your workbook which we will get into later. Another thing it contains would be the search bar, where you can quickly search for any tool in excel that you just cant seem to find.
Uh oh! Your Excel file just crashed. Don’t worry, in the header their a feedback icon. Make sure to include a screenshot because a little birdie told me that your issue might get responded to faster that way :)
- Ribbon — Moving on to Ribbons! If you double click on the Ribbon section you can hide the entire section. This section contains tabs that group tasks like, “Home,Insert,Data,Formulas…” and more. Each group is further divided by sub-sections like grouping stuff that have to do with fonts in the Home section. One more thing! Their are tabs called contextual tabs that only reveal themselves under certain actions like clicking a chart.
- Formula Bar — If it wasn’t obvious by the name. A Formula bar is where your formula for the respective cell will be located. In this bar you can see and edit the formula that was applied to that cell. In the next article I'm going to go deeper into what specific formula’s we can use to make our lives much easier.
- Worksheet — Ah yes. The bread and butter of an excel file. The worksheet. This is where most of your information will be contained. Each cell on the worksheet contains a name box and each workbook can contain multiple sheets. Remember when I told you earlier how much a single worksheet can hold? Yeah things just got a little more bigger. By clicking the plus button at the bottom right we can add more worksheets. If we have to many worksheets we can use the navigation button also at the bottom to scroll through them.
- Status Bar — This bar contains information about accessibility. We can also toggle through different worksheet views. A neat feature is if you highlight cells that only contain numbers you will be able to see some basic statistical information about them like average min and max. If you want to add more to the status bar you can right click it and turn on or off certain features you want or don’t want.
Navigating Worksheets and Workbooks
A workbook is a file that contains one or more worksheets. Think of a workbook as a book and each worksheet as a page within that book. To get started, open Excel and create a new workbook by selecting ‘File’ > ‘New’ > ‘Blank Workbook’.
When the worksheet gets too big, we are given scroll bars on the right-hand side to scroll all the way down to our desired cell destination. If that’s not your cup of tea, you can also use the arrow keys to move around between cells.
At the bottom of the workbook, you’ll find tabs for each worksheet. Click on these tabs to switch between different sheets within the same workbook. Besides having multiple sheets, we can also have multiple workbooks. You can go to the View tab in the Ribbon Section we spoke about before and then select ‘Switch Windows’ to select your desired workbook. “Alt” keys can also be used for shortcuts as well to navigate more smoothly.
Data Entry and Editing
Click on a cell to start entering data. You can input text, numbers, and dates directly into the cells. To edit data, double-click the cell or select the cell and make changes in the formula bar at the top of the screen.
Tired of having to rewrite the same formula for every cell? Use the AutoFill feature to quickly fill cells with repetitive data. Click and drag the small square at the bottom right corner of a selected cell to apply the same data to adjacent cells.
Let’s say you’re at your data entry job and your boss gives you the list of all those attending the Microsoft world wide event which hosts thousands of people. It’s your job to enter each one of those that will be attending. Oh boy, we’re going to be here forever, and I might make a typing mistake and get fired. Fear not! Excel has this nifty feature called forms.
To get started, search for “form” in the search bar for the header. Click to add new data to the columns, and if you made a mistake, click restore. If you want to search for data, you can click on criteria and do your thing. Awesome, isn’t it?
What happens if you type in a value that wasn’t supposed to be in the form, like a negative number when all numbers should be positive? Fear not! Excel also allows you to do data validation! Click ‘Data’ > ‘Data Validation’ > ‘Allow whole numbers’ and then choose your range. If we choose greater than 0, then every time we enter a negative number or 0, a pop-up will appear indicating an error.
We can make this whole process a lot easier by also taking advantage of something called macros and VBA. This allows us to automate some tedious tasks. For example, let’s make a button that, once clicked, will always open up the form. First, go to your Developer tab, then click ‘Record a Macro’. Open up the form and then close it. Then stop recording and you’re done!
Oh wait, if your data doesn’t start at the top right corner, then you’re going to have to specify where it starts using Visual Basic. Don’t worry, it’s just going to be one line of code. If it starts in cell A6, then it would be Range("A6").CurrentRegion.Name = "database". Close Visual Basic and you're done. Now, let's add a button by going to 'Insert' in the Developer tab, then add the recorded macro you just created. Now we just automated the form processes!
Basic Formatting
Customize your data’s appearance by changing the font size, color, and style. Use the options in the ‘Home’ tab to apply these changes. You can also format numbers as currency, percentages, dates, etc., by selecting the desired format from the ‘Number’ group in the ‘Home’ tab.
Finally, enhance your spreadsheet’s readability by adding borders and shading. Select cells, go to the ‘Home’ tab, and use the ‘Borders’ and ‘Fill Color’ options to apply these effects. By adding text alignment and fill colors, your worksheet becomes more presentable to stakeholders.
We’ve covered the basics of Excel, including navigation, data entry, and formatting. Practice these skills to become more comfortable with Excel and explore additional features as you progress. These were just the very surface of what's out their in excel. We only covered what we see with our own eyes. In the next article we will go one layer deeper and discover how we can use the concept of formulas to generate insights into the data we have. Share your thoughts, ask questions, or suggest topics for future articles in the comments below.
