Hey! Still reading those Dummies books and struggling to work your way through, Don’t worry. We’ve got you covered!!
This article will demystify you and help you walk through the basics of Excel and make your journey to learn Excel a lot simpler and enjoyable.
Making your basics crystal clear is essential if you want to understand the advanced functionality of Excel.
So, let’s get started with the building blocks of Excel - An Excel Worksheet
A Single Spreadsheet
It is a single spreadsheet consisting of rows and columns. It is basically an element of Excel Workbook. By default, the name of the first worksheet is ”Sheet1”. So, a workbook is like an entire textbook while worksheet is a single page within that book.
Basic Intro To Microsoft Excel its a worksheet is in a tabular format consisting of rows and columns. Row numbers are placed vertically at the left side of the sheet and range from 1 to 1048576 whereas column headers are placed horizontally at the top of the sheet and range from A to XFD.
An intersection of a row and a column is called Cell and is identified with a combination of column headers and row numbers like A10 or C76.
it is the entire Excel file that contains one or more sheets. A simple Excel Workbook can be of a type - .xls or .xlsx.
To create a new workbook, follow the steps below:
- Open Excel and Click on File
- Click New, Under Featured, click on Blank workbook.
Basic Intro To Microsoft Excel At the top of the Excel Window, there is a strip of buttons and icons called Ribbon. It helps you navigate and choose from various commands of Excel.
An Excel Ribbon has five parts - Tabs, Groups, Command, Quick Access Toolbar and Dialog launchers. Ribbon consists of various tabs like Home, Insert, Page Layout, Formulas, Data, Review, View. Within each tab, there are several groups of commands.
Home is a tab and within the Home Tab there is a group called “Alignment” and under alignment there is a command “Wrap Text”. The fourth component - A Quick Access Toolbar is a customizable toolbar to which you can add most frequently used commands.
Lastly, a dialog launcher is a small downward pointing arrow in the bottom right of each group. It is used to open a dialog box that can be used to input information and make choices about different aspects of the current worksheet or its content.
Basic Intro To Microsoft Excel Entering Data in Excel
Now that we have covered the basic terminology that is used in Excel, we will shift our focus onto the process of entering information or functions in Excel. To enter data in Excel, simply select a cell and start typing. The text will appear on the cell as well as on the formula bar at the top.
After entering the information, press Enter to submit the data and move to the cell below or press tab to submit the data and move to the cell on the right. To enter data on a new line within a cell i.e. to insert a line break, press Alt + Enter.
Types of data in excel
The three main types of data are Text, Number and Formulas.
Text is mainly used to provide headings to tables/columns, write descriptions, etc. and can contain letters, numbers, special characters. It is by default aligned to the left side of the cell. Note: To enter a numerical value or formula as a text, type an apostrophe before it.
Number is used for calculations and these are aligned towards the right side of the cell. This type of data includes integers, decimals, percentages, currency, accounting, etc. To enter a date in Excel, type the date in the default format mm/dd/yyyy or whichever format is selected for that cell. To change the format, simply press Ctrl + 1,
Then in the dialog box select date and select the appropriate format. Note: Dates and Time are also stored as numbers in Excel. January 1, 1900 is stored as number 1 and January 2, 1900 is stored as number 2 and so on.
You can increase or increase the decimal points using the icons under the number section of the Home tab again.
Formulas are mathematical equations that direct Excel to perform calculations. Each formula has a specific syntax that users must follow to make that function work. It uses hard coded numbers and values in other cells to do calculations.
Examples are =32*(12+10) or =A2*B4 or =A2*(12+C4)
* There are several custom number formats that can be applied to the data in Excel. Let’s look at some of the examples below:To convert a number to a specific currency, select the cell and then click on the small arrow next to “$” icon under Number section in Home tab. From the drop-down list, select the desired currency.
For further customization, press Ctrl + 1 and in the dialog box, select “Currency”. From here, you can select the currency from the symbol dropdown list, increase or decrease the decimal point and select the formatting for negative values. A sample of your selection will also be displayed in the box.
* To convert a number into percentage, simply press the “%” icon under Number section in Home tab. This will multiply the number by 100 and add a percentage sign to the display.
* To convert a number into accounting format, simply select Accounting from the dropdown in number section.This will add $ sign and two decimal places.
* To apply comma style, simply press the “,” icon under Number section in Home tab. This will add commas to large numbers and two decimal places (so 1000 becomes 1,000.00).
* To convert a number to Zip code +4, Press Ctrl + 1 and under category select “Special”. This will convert number to the pattern 00000-0000. Other options like social security number and phone number are also available in the same dialog box.
* If you want to add units to a number, you can do that using Custom Formatting. For example, in a table you want figures in Millions, and you want the word “Million” written next to the number with a $ dollar sign. (Convert 76 to $76 Million). To do that, Press Ctrl + 1 and select Custom and in the type field write: $General" Million". It will make the data look like text, but they are still a number.
* To add color to your number type, go to Custom in Format Cell dialog box. Write color name enclosed in square brackets before the number format. If the current formatting is $#,##0, to add blue color the format will now be [blue]$#,##0.
Basic Intro To Microsoft Excel writing a formula in excel
To write a formula begin with an = or + sign and use numbers or cell references in order to indicate which values are to be used in the calculation. Follow the steps below:
- Type = or +
- Enter cell reference like A12 or F8
- Enter an operator like +, -, * or /
- Enter the second cell reference. Continue if necessary
- Hit Enter
Consider an example where there is a list of sales for 12 months and total annual sales needs to be calculated. This can be done using excel formula.
There are two ways of doing it –
1. Create a summation formula where you add in all the numbers by typing them. Example: =1787092+2467847+…….+2359196. Even though this formula will give you a correct answer, it will not be dynamic.
This formula needs to be changed each time there is a change in the set of monthly sale values.
2. Create a formula that sums the value using cell addresses. Instead of inserting the number we will give the cell reference that contains the number. Example: =SUM(B2:B13)
With this formula, the values that are inserted in the cell will be used for calculation making the formula dynamic.
In this second example below, we need to calculate fixed cost per unit. We have the total fixed cost entered in cell B2 and number of units in cell B3. The result should be displayed in cell B4.
So, we will select cell B4 and write the formula =B2/B3 to get the amount of fixed cost per unit.
After creating this formula, even if you change the value in cell B2 or B3 the result in cell B4 will be updated automatically based on the new values.
Note: You can see that even though the result i.e. $1720 is shown in cell B4, the formula i.e. =B2/B3 is visible on the formula bar. You can even edit the formula directly from the formula bar.
In this example we have used the formula to perform division, we can add, subtract or multiply the same way by simply changing the plus division to an appropriate operator in the formula.
Basic Intro To Ms Excel ,The operator follows a default order in which the calculation occurs. The order of operators is as follows:
3. Multiplication and Division
4. Addition and Subtraction
So, if we use this formula =6+7*2, Excel will first multiply 7 by 2 and then the result i.e. 14 is added to 6. The result will then be 20.
For more information on formulas, click here<<link to Excel Formulas article>>
Basic Intro To Microsoft Excel Format data as table
Basic Intro To Microsoft Excel If we have a list of information entered in Excel and want to format it as a table, we should follow the steps below:
* Click on any cell in the table and Press Ctrl + A to select the entire table.* To apply borders, Go to Home Tab > Under Font Group, Click on Borders icon > From the dropdown Select All.
* Select the Headers of the table and under Home Tab and Font Group, Select B (to make the header bold), fill color grey and change the font color to white.
* Select Column F and G and change number formatting to currency. Go to Home Tab> Under Number Group > Click on the Dollar Sign > Select the appropriate currency.
Press Alt + W + V+ G to remove gridlines and your table with formatting is ready!
Basic Intro To Micosoft Excel Formatting in excel
Basic Intro To Microsoft Excel ,The main goal of any Excel Worksheet to make the data presentable, attractive and to be able to understand the data quickly. All this can be done using Excel formatting! The points below will guide you with the different ways of formatting the content:* Change Font – By default the font is set to Calibri. To change the font, go to Home Tab > Click on the arrow next to the Font Command > Select the appropriate Font. If you hover on the different options in the dropdown, a live preview of the same will be visible.
* Change Font Size - To change the font size, go to Home Tab > Click on the arrow next to the Font Size Command > Select the appropriate Font Size. You can click on either “Increase Font Size” or “Decrease Font Size” to change the size.
* Change Font Color - To change the font color, go to Home Tab > Click on the arrow next to the Font color Command > Select the appropriate Font color.
* Use Bold, Italics, Underline – Select the cell where you want to apply the formatting. Press Ctrl + B to make text bold, Ctrl + I to make it italics and Ctrl + U to make it underlined. Or else, you can simply click on “B”, “I” or “U” symbols under Home Tab.
* Change cell color – Select cell > Click on Home Tab > Click on arrow next to Fill Color > Pick the color you want. Or simply press Alt + H + H and pick the color you want.
* Apply pattern – Select cell > Click on Home Tab > Click on Format Cells dialog launcher > Under Fill Tab > Select Pattern Color and Pattern Style.
* Change Horizontal and Vertical Alignment – Select Cell > Under Home Tab > Select Top, Middle or Bottom alignment to change vertical alignment and Select Left, Middle or Right Alignment to change horizontal alignment.
* Apply cell style - Select the cell > Home tab > Click on cell style > Choose the desired style from the drop-down menu. Note: Once you select a cell style, all existing formatting except text alignment will be replaced.
* Wrap Text - If the text entered in a cell cannot be fully visible in a single line, we can select the Wrap Text function from Home Tab to make the data fit properly.
* Apply Number Formats - Under Home Tab > Click on the dialog box launcher, it will open the Format Cells dialog box where a complete list of number formats is available on the Number tab. You can select the desired format from a plethora of options including numbers, dates, times, fractions, percentages, and other numeric values.
* Add Borders - To add borders, select the cell and click on the borders icon under Home Tab. Select the appropriate border line and color.
* Format Painter - To copy paste a formatting in one cell to another, simply select the cell with formatting and click on the format painter icon in Home Tab and then click on the cell where you want to paste the formatting.
Note - Once you click on the format painter icon the arrow will be replaced with format painter icon.
* Clear Formatting - To clear formatting in a cell, Select the cell > Go to the Home tab > Go to the Editing group > Click on the arrow next to the Clear button > Select Clear Formats.
Basic Intro To Microsoft Excel Printing Work Sheet in excel
Now that your data is ready, you will need to print it. So, let’s move to the process of how to print a worksheet and the print setting available in Microsoft Excel.
The following points will guide you through the same:
* To print a worksheet, Click on File tab > Click on Print. In the dialog box, we will be able to see a preview of the page that will be printed, and we can click on the big Print button to start printing.
Note: At the bottom of the dialog box, click on previous or next button to preview other pages.
* To print multiple pages, hold down CTRL + click the name of each workbook to print, and then click Print.* To print multiple copies, increase the number of copies from 1 to the desired number. You can also select collated or uncollated option from the dropdown. Note: Collated prints all pages of the 1st copy and then 2nd and so on whereas uncollated prints all copies of 1st page, then 2nd page and so on.
* To print only the active worksheet, click on “Print Active Sheet” from the dropdown under settings option.
* Page orientation can also be changed (landscape or portrait) from the dropdown available.
* To adjust page margins, either select from the pre-defined options (Normal, Wide or Narrow) Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines to manually change the page margins.
* If the entire table needs to be printed on 1 page, it can be done by selecting the “Fit Sheet on One Page” option from the scaling drop-down list. Other options available are “Fit All Columns on One Page” and “Fit
* To set a print area, Select the cells you want to print > go to Page Layout > Click on Print Area > Select Set Print Area.
Basic Intro To Microsoft Excel Save an Excel File
To save an Excel file, click the Save icon in the top left of Excel or simply press Ctrl + S. A Save As dialog box will open, on the left panel click on “Save As”. Under Save As, you can see four items: OneDrive, This PC, Add a Place, and Browse. By default, “This PC” option is selected and you file will be saved in “My documents”.
If you want your file to be saved in My Documents folder, type a file name and click on Save. Else, click on the “More options…” link and select the desired folder, give a file name and click on Save icon.
Below the File name box
There is drop-down from which you can select the file type like.xlsx or.xlsm etc.
In this article, we have tried to cover the basics of what Excel can do and it’s not even 10% of it. There are many functions and formulas of Excel that can help you automate your work and make you faster and more efficient in Excel.Follow us for more articles and tutorials on Microsoft Excel!