MVP #47: Microsoft Excel Book. Chapter 1 Part 1

, , No Comments
It's not easy juggling clients' work, blogging daily, replying mails, keeping up with my MBA studies, managing a small team and writing a book. 

Today, I am happy to share with you the first part of the chapter 1 of the Microsoft Excel book I am writing. I am considering titling it Microsoft Excel for The Business Professional.

Also, I have finally put everything needed in place to start the next batch of our online Excel training. If you chose it as your preferred Valentine Gift offer in the February 14 blog post I made, then be ready for a welcome mail soon. If you want to join and take the In-depth Microsoft Excel and Business Data Analysis online training, the price is no longer N25,000. It is now N30,000. You can sign-up here.

Microsoft Excel: Meet the Menus

In Microsoft Excel, we’ve got the Home menu, Insert menu, Page Layout menu, Formulas menu, Data menu, Review menu and View menu.

The other menus — Developer menu, Load Test Menu and Power Query menu — are not displayed by default. I enabled them and later in the book I will explain what they do and how to enable them.

Home Menu
The home menu is Excel’s most used menu. It has very straightforward sub-menus.

Clipboard: Allows you to copy, cut and paste in Excel
Font: Allows you to set font size, color, background color (fill) & turn on bold or italics or underline.
Alignment: Allows you to set the position of whatever you’ve typed (or copied) into Excel. It also allows you to set how it’s written: horizontal, vertical or slanting.
Number: Allows you to set how a number is shown in Excel: regular number, currency, scientific, percentage, fraction… 
Styles: Allows you to set the format of an Excel cell based on the data it holds (conditional formatting). It also allows you to convert a selection of cells to table, and to set quick formats for a cell.
Cells: Allows you to insert new cells, delete cells and change cell format.
Editing: It houses the very useful Sort and filter tools. And also Find & Select, Find & Replace. There’s also AutoSum which helps you sum all numbers in a selection.

Insert Menu
The Insert menu houses some of Excel’s best tools.

Tables: Allows you to insert PivotTable, PivotChart and Table. Inserting a table in Excel allows for quick formatting, and better formulas (via named ranges). PivotTable and PivotChart will be discussed later.
Illustrations: Allows you to insert images and shapes.
Charts: Allows you to insert charts, which will be specially discussed later.
Tours: Houses Map which takes you straight to Power Map. Power Map is part of Microsoft’s new Power BI. It enables you make geo-maps and create amazing data visualization tours.

Reports: Lets you access Power View, another Power BI tool. Power View allows to create a data model, loading up many different databases and creating analysis that cuts across all the databases, allowing you to see insights that are beyond a single database.
Sparklines: Allows you to insert charts that fit into one Excel cell. Makes some reports beautiful and easy to read.
Filter: Allows you to filter out field values you are not interested in.
Links: Allows you point a cell content to a website or an email address.
Text: Allows you to insert texts and objects (pretty much anything, including a PDF document)
Symbols: Allows you to type out equations and special symbols.

Page Layout Menu
The Page Layout menu does just that: setting up your Excel document’s page look and for printing.

Themes: It’s not often used; it sets the look of the Excel window itself. 
Page Setup: It allows you to set how the page comes out when printed. Most used are the Orientation (to set as Portrait or Landscape) and Print Area (to select on the cells you want to print).
Scale to Fit: It allows you to set how much is printed per page. Most frequent use is to force Excel to print on one page, or fit all the fields (columns) on one page width.
Sheet Options: You wouldn’t want to change the default. It allows you set whether Excel gridlines be printed or not, and headings too. Default is no/off (unticked).
Arrange: It lets you rearrange overlapping objects (shapes, images, textboxes…). Or align them.

Formulas Menu
The Formula menu gives you access to Excel’s built-in formulas.

Function Library: It has the formulas grouped by category. Once you have an idea of what you want done, it helps you locate the formula to use. It’s good to look through it once in a while to have an idea of the out-of-the-box analysis Excel can do.
Defined Names: Lets you name a cell or selection of cells. Can be very useful when analyzing a big database or building a model.
Formula Auditing: Allows you to check for errors in your formulas, trace formula cells and see how your final result is being calculated.
Calculation: Allows you to set when the formulas in your Excel sheet are calculated: automatic (whenever a cell value changes) or manual (at first entry and when you force them to be recalculated).

Data Menu
The Data menu allows you to work with external data and do basic data formatting.

Get External Data: It allows you to import or link to an external data file (non-Excel file). You’ll use it whenever you have a data in text file and need it worked on in Excel.
Connections: Allows you to make changes to the connections/links to an external data file. Or force a refresh of the connections to capture changes made in the external data file since last connection.
Sort & Filter: Allows you to sort data and do some filtering too. Filter allows you to specify values to display.
Data Tools: Allows you do very basic data analysis. Especially removing duplicate entries, and splitting one field into several (text-to-column). Example is splitting full name into First name and Last name.
Outline: Allows you to group (and hide) several rows. Useful for large data reports with few categories; helps to group categories.
Analysis: This is only visible after you enable Data Analysis add-in or Solver add-in. It allows you access a large collection of statistical analysis tools and problem modeling.

Review Menu
The Review menu is for spell checks, commenting and setting access restrictions.

Proofing: Allows you to carry out spell checks and word meaning checks.
Language: Allows you to translate the Excel file content from one language to another.
Comments: Allows you to include comments in an Excel sheet, view all comments at once or delete comments.
Changes: Allows you to set access restrictions and track changes to the Excel file. Also allows you to share the file.

View Menu
The View menu allows you to change the window layout of the Excel document. It doesn’t change anything in the actual document, just the way it’s displayed.

Workbook Views: Allows you to set how the workbook (Excel file) is displayed.
Show: Controls what non-printing details are shown: Gridlines, Headings, Formula bar and Ruler. The one you’ll be interested most in is Gridlines. If you want your Excel sheet to look more like a Word file, untick the Gridlines. That’s what’s done to every Excel sheet you see that has no Gridlines.
Zoom: Does what it says: sets zoom.
Window: Allows you to freeze headers so when you scroll they will never be out of view. And also allows you to split the Excel sheet display, so you can compare two different parts of the sheet.
Macros: Allows you to see the macros programmed in the Excel file (if there’s any macro in it). 

And I will like to say a big thanks to everyone who sent me helpful suggestions on how to go about writing an effective book. 


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.