Basics of Excel

 

This section explains the basics of Excel. Don't miss our sections on Functions and Data Analysis to discover the real power of Excel!

1 Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to use the ribbon.

2 Workbook: A workbook is another word for your Excel file. When you start Excel, click Blank workbook to create an Excel workbook from scratch.

3 Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

4 Format Cells: When we format cells in Excel, we change the appearance of a number without changing the number itself.

5 Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.

6 Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used.

7 Data Validation: Use data validation to make sure that users enter certain values into a cell.

8 Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.

9 Print: This chapter teaches you how to print a worksheet and how to change print settings.

10 Share: Learn how to share Excel data with Word documents and other files.

11 Protect: Encrypt an Excel file with a password so that it requires a password to open it.

Take your Excel skills to the next level! 🚀 You can find related examples and features on the right side of each chapterat the bottom of each chapter. Below you can find a complete overview.


1. Ribbon

- Formula Bar: The formula bar in Excel displays the value or formula of the active cell. You can use the formula bar to enter or edit formulas.

- Quick Access Toolbar: If you use an Excel command frequently, you can add it to the Quick Access Toolbar. By default, the Quick Access Toolbar contains four commands: AutoSave, Save, Undo and Redo.

Add to Quick Access Toolbar in Excel

- Customize the Ribbon: The ribbon in Excel can be customized. You can easily create your own tab and add commands to it.

- Developer Tab: Turn on the Developer tab in Excel if you want to create a macro, export and import XML files or insert controls.

- Status Bar: The status bar in Excel can be quite useful. By default, the status bar at the bottom of the window displays the average, count and sum of selected cells.

- Checkbox: Inserting a checkbox in Excel is easy. For example, use checkboxes to create a checklist or a dynamic chart.

2. Workbook

- Themes: Excel offers themes to change the look of your workbook with the click of a button. Each theme consists of 12 colors, two fonts (Headings and Body) and effects for shapes and SmartArt.

Themes in Excel

- View Multiple Workbooks: If you want to view multiple Excel workbooks at the same time, execute the following steps.

- AutoRecover: Excel periodically saves a copy of your Excel file. Learn how to recover a file that was never saved and how to recover a file that has been saved at least once.

- Merge Excel Files: To merge Excel files into one, use the 'Move or Copy' feature. If you need to merge many Excel files, consider using VBA to automate the process.

- Save in 97-2003 Format: If you save your Excel files in 97-2003 format (.xls instead of the new .xlsx format), users who do not have Excel 2007 or later installed on their computer, can still open your Excel files.

3. Worksheets

- Zoom: In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection.

- Split: You can split your Excel worksheet into panes to view multiple distant parts of your worksheet at once. Simply use the split bar to change the size of each pane.

- Freeze Panes: If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

Freeze Top Row in Excel

- Group Worksheets: Group worksheets in Excel to edit multiple worksheets at the same time. Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet.

- Consolidate: You can use Excel's Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet.

- View Multiple Worksheets: If you want to view multiple Excel worksheets at the same time, execute the following steps.

- Get Sheet Name: To return the sheet name in a cell, use CELL, FIND and MID in Excel. There's no built-in function in Excel that can get the sheet name.

- Comments: Insert a comment in Excel to start a conversation or to give feedback about the content of a cell. Old style comments are still available and are now called notes.

- Spell Check: Learn how to check the spelling of text in Excel, add words to your custom dictionary (or AutoCorrect list) and learn how to change spell checking options.

- Unhide Sheets: Unhiding sheets in Excel is easy. A workbook must contain at least one visible worksheet.

- Chart Sheet: You can find most charts on the same worksheet as the source data (embedded charts). However, you can also move a chart to a separate sheet that only contains a chart (chart sheet).

4. Format Cells

- Decimal Places: Learn how to show fewer or more decimal places in Excel without changing the number itself. You can also round a number.

- Fractions: This example teaches you how to enter a fraction in Excel and how to change the format of a fraction.

- Currency vs Accounting: The Currency format and the Accounting format in Excel are very similar to each other. This example shows the difference.

- Text to Numbers: In Excel, text is left-aligned and numbers are right-aligned. This example teaches you how to convert 'text strings that represent numbers' to numbers.

- Numbers to Text: In Excel, numbers are right-aligned and text is left-aligned. This example teaches you how to convert numbers to 'text strings that represent numbers'.

- Custom Number Format: Excel has many built-in formats that you can use: Currency, Accounting, Date, Time, Percentage, etc. If you cannot find the right format, you can create a custom number format.

- Format Painter: The Format Painter is one of the most underused features of Excel. The Format Painter copies formatting from one place and applies it to another.

Format Painter in Excel

- Cell Styles: Quickly format a cell by choosing a cell style. You can also create your own cell style in Excel.

- Wrap Text: Wrap text in Excel if you want to display long text on multiple lines in a single cell.

- Merge Cells: Merge cells into one large cell to make clear that a label in Excel applies to multiple columns. Use CONCATENATE, TEXTJOIN or Flash Fill to merge cells without losing data.

- Strikethrough: This example teaches you how to apply strikethrough formatting in Excel. You can still read text with a strikethrough effect.

- Superscript and Subscript: It's easy to format a character as superscript (slightly above the baseline) or subscript (slightly below the baseline) in Excel.

- Check Mark: To insert a check mark symbol in Excel, simply press SHIFT + P and use the Wingdings 2 font.

Check Marks in Excel

- Remove Leading Zeros: This guide teaches you how to remove leading zeros in Excel.

5. Find & Select

- Find Features: This page illustrates useful find features in Excel. Learn how to perform workbook-wide searches, case-sensitive searches, find exact cell content, and use wildcards.

- Wildcards: Excel has 3 wildcards. A question mark (?) matches exactly one character. An asterisk (*) matches zero or more characters. And a tilde (~).

- Delete Blank Rows: This example teaches you how to delete blank rows or rows that contain blank cells.

Remove Empty Rows in Excel

- Row Differences: This example teaches you how to quickly highlight cells whose contents are different from the comparison cell in each row.

- Copy Visible Cells Only: By default, Excel copies both visible and hidden cells. However, it is possible to copy visible cells only.

- Search Box: This example teaches you how to create your own search box in Excel.

6. Templates

- Budget: This example shows you how to create a budget in Excel. Follow this step-by-step guide and take control of your finances.

Budget in Excel

- Calendar: This example describes how to create a calendar in Excel (2024 calendar, 2025 calendar, etc). If you are in a hurry, simply download the Excel file.

- Holidays: This example teaches you how to get the date of a holiday for any year (2024, 2025, etc).

- Meal Planner: This article shows you how to create a meal planner in Excel. Use drop-down lists to easily select meals for breakfast, lunch, and dinner.

- Invoice: This article describes how to create a simple invoice in Excel.

- Automated Invoice: This article describes how to automate the generation of invoices in Excel.

- Default Templates: Book.xltx and Sheet.xltx are two special templates you can create and add to the XLStart folder. As a result, Excel uses Book.xltx as the basis for new workbooks and Sheet.xltx as the basis for new worksheets.

- Time Sheet: This example teaches you how to create a simple timesheet calculator in Excel.

Time Sheet in Excel

- BMI calculator: Create a BMI calculator in Excel and find out if you are a healthy weight. Body mass index (BMI) is a measure of body fat based on weight and height that applies to adult men and women.

7. Data Validation

- Reject Invalid Dates: This example teaches you how to use data validation to reject invalid dates.

- Budget Limit: To avoid exceeding a budget limit in Excel, simply use data validation and the SUM function.

- Prevent Duplicate Entries: To prevent duplicate values from being entered into Excel, use data validation and the COUNTIF function.

- Product Codes: Use data validation to only allow the input of specific product codes. For example, only allow codes that are 4 characters long, start with a C, etc.

- Drop-down List: Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values.

Drop-down List in Excel

- Dependent Drop-down Lists: This example describes how to create dependent drop-down lists in Excel.

- Cm to inches: 1 cm = 0.3937 inch and 1 inch = 2.54 cm. Use a simple formula, the CONVERT function or download our free unit converter to convert from cm to inches or vice versa.

- Kg to lbs: 1 kg = 2.20462 lbs (pounds) and 1 lb (pound) = 0.45359 kg. Use a simple formula, the CONVERT function or download our free unit converter to convert from kg to lbs or vice versa.

8. Keyboard Shortcuts

- Function Keys: Function keys in Excel allow you to do things with your keyboard instead of your mouse to increase your speed.

- Insert Row: To quickly insert a row in Excel, select a row and use the shortcut CTRL SHIFT +.

- Save As: Use the shortcut F12 to display the Save As dialog box in Excel. Press CTRL + s to save an existing workbook. It's good practice to periodically save while you are working on your Excel file.

- Delete Row: To quickly delete a row in Excel, select a row and use the shortcut CTRL - (minus sign).

- Formula to Value: To quickly convert a formula to a value in Excel or to convert multiple formulas to values, execute the following steps.

Convert Formulas to Values in Excel

- Scroll Lock: When Scroll Lock is turned on, you can use the arrow keys to scroll through the worksheet without changing the active cell. Excel displays Scroll Lock in the status bar.

- Bullet Points: This page illustrates 4 ways to insert bullet points in Excel.

- Line Break: You can insert a line break in Excel by pressing Alt + Enter. When concatenating strings, use CHAR(10) to insert a line break.

- Show Formulas: By default, Excel shows the results of formulas. To show the formulas instead of their results, press CTRL + ` (you can find this key above the tab key).

Show Formulas in Excel

- Paste Special: Use the shortcut Ctrl + Alt + V to display the Paste Special dialog box in Excel. Next, press an underlined letter to paste formulas, values, formatting, etc.

9. Print

- Workbook Views: Excel offers three different workbook views: Normal, Page Layout and Page Break Preview.

- Page Breaks: Insert a page break in Excel to specify where a new page will begin in the printed copy.

- Headers and Footers: This example teaches you how to add information to the header (top of each printed page) or footer (bottom of each printed page) in Excel.

Add Header in Excel

- Page Numbers: This example teaches you how to insert page numbers in Excel.

- Print Titles: You can specify rows and columns in Excel that will be printed on each printed page. This can make your printed copy easier to read.

- Center on Page: To center a range of cells on a printed page in Excel automatically, execute the following steps.

- Print Gridlines & Headings: Print gridlines (the horizontal and vertical lines on your worksheet) and row/column headers (1, 2, 3 etc. and A, B, C etc.) to make your printed copy easier to read.

- Print Area: If you set a print area in Excel, only that area will be printed. The print area is saved when you save the workbook.

10. Share

- Embed: Embed an Excel worksheet in a Word document and work with Excel in Word. You can also embed files in Excel.

- PDF: This page teaches you how to convert an Excel file to a PDF file.

Convert Excel to PDF

- Share Workbooks: If you share a workbook in Excel 2016 or older versions, you can work with other people on the same workbook at the same time.

- OneDrive: Save your Excel files to your OneDrive location and access your files from any computer or share them with other people.

- Online: You can edit Excel files with Excel Online from anywhere without having Excel installed on your computer.

- Import Access Data: This example teaches you how to import data from a Microsoft Access database. In Excel, when you import data, you make a permanent connection that can be refreshed.

- Microsoft Query: This example teaches you how to import data from a Microsoft Access database by using the Microsoft Query Wizard. With Microsoft Query, you can select the columns of data that you want and import only that data into Excel.

- Import/Export Text Files: This article describes how to import or export text files. Text files can be comma separated (.csv) or tab separated (.txt)

- XML: Convert your XML file to an Excel file, or vice versa. This enables you to exchange data between different applications.

11. Protect

- Protect Workbook: This example teaches you how to protect the workbook structure in Excel.

- Protect Sheet: When you share an Excel file with other users, you may want to protect a worksheet to help prevent it from being changed.

- Lock Cells: You can lock cells in Excel if you want to protect cells from being edited.

Select All Cells in Excel

- Read-only Workbook: This example shows you how to make your Excel workbook read-only.

- Mark as Final: Mark a workbook as final in Excel to indicate that this is the final version of your workbook. Only use this feature to discourage editing. Users can still edit the workbook.

Visit our next section: Functions.