There are also commands on each tab that have been

CHAPTER7 organized into GROUPS. Some groups have an arrow in the

bottom right corner that you can click to view even more
1. SOLVING REAL-LIFE PROBLEMS IN EXCEL available commands.
If you ever feel like the ribbon is taking up too much
You’ll need skills to solve the problem-some planning,
space, you can always hide or minimize it. Click the arrow in
some basic math and a little knowledge of how formulas
the upper right corner then choose the option you want.
work. You can get a lot farther than you might think.
(Auto Hide Ribbon, Show Tabs, Show Tabs and
Commands.)
Say you're at work and your boss comes to you with a
Also in the upper right corner is a place where you can
problem. Apparently, there's an event coming up and 614
access your Microsoft Account. Here you can update your
people have already RSVP’s unfortunately the venue only
photo,manage you account settings, or switch to a different
seats 550. So how many folding chairs are we going to need
account altogether.
to seat all the extra guests? Like a lot of real life problems.
You can even customize your copy of excel to make
There's actually enough data here to turn this into a math
certain commands more convenient. Take the QUICK ACCESS
problem. You could even think of it as a word problem.
TOOLBAR in the upper left corner. This area gives you access
to frequently used commands like save and undo , no matter
Susie has 14 apples. She gives five of them away. All we
where you are on the ribbon. To add more commands, just
have here is a simple subtraction problem. One that you
click the tiny arrow next to the toolbar then choose the ones
might even be able to solve in your head. Just take the
you want.
number of people coming, subtract the number of seats at the
In Excel 2016, you’ll also find a new feature called TELL
venue and that'll give us answer. We're going to move the
ME. It works like a search bar to help you quickly find tools or
equal sign to the beginning and with this one tiny change its
commands you want to use. Just type in what you want to do
now in a format that Excel can understand.
in your own words. The results will give you a few options
You could even change the numbers to cell references. and then guide you through the process as well as offering
Let's take a look at the answer. Looks like we're going to need additional resources.
64 extra folding chairs. And because we use cell references
we can change the number of people attending and the WORKBOOK also called as worksheet spreadsheet. Every
answer will update automatically. worksheet contains cells, columns, and rows for entering
data, plus the formula bar for working with your data. If you
Sometimes getting from problem to formula is easier need to scroll up or down, or even side ti side you can use the
than you thought it would be like you just saw. Then there are SCROLL BARS.
times when you get stuck somewhere in the process and In the bottom right corner, you’ll find several tools that
you're not sure how to continue. The important thing to let you change the way your worksheet is displayed. To zoom
remember is that you're still most of the way there even if in or out, click and drag the ZOOM CONTROL. The number
you're feeling unsure. You just have to break the problem next to the slider will tell you what the zoom percentage is.
down into more manageable chunks. You can also switch between different worksheet views
using the commands on the lower right corner of the screen.
The truth is there's no formula for writing formulas. But  NORMAL is selected by default. This mode is great for
that's okay. The journey from point A to point B is rarely as everyday tasks like entering and editing data.
treacherous as it seems sure there might be some issues  PAGE LAYOUT shows you what the worksheet will look
along the way, you might run into a problem that you're not like on the printed page. You can also add headers and
sure how to solve but remember you already know more footers here.
than you think you do. That's the key to solving real life  PAGE BREAK PREVIEW makes it easy to change the
problems in Excel. location of page breaks in your workbook. Useful when
printing lots of data.
2. Getting Started
Excel is a spreadsheet program that allows you to store, BACKSTAGE VIEW
organize and analyze information. You can use it to create You can get there by clicking the File tab on the far left
contact lists, budgets, invoices and just about anything else of the ribbon. Here you can access all kinds of information
ou can imagine. related to your current workbook and more. Includes
When you open Excel, there’s a good chance you’ll be commands like New, Open, Save and Print. This makes it easy
taken to the start screen first. From here you can access to find what you’re looking for and review your options all in
recent workbooks or create something new-either from one place.
scratch, or from a template. 3. Creating and Opening Workbooks
RIBBON which is the collection of tools and features at Most feature in Microsoft office, including excel are
the top of the screen.The ribbon is divided into tabs like Page geared towards saving and sharing files online. This is done
Layout,Insert and Home, so you can easily find what you’re with OneDrive, which is an online storage space for your
looking for. documents and files, so you can access them even when
you’re away from your computer. If you want to use Now, you can save it at any time by clicking the icon.
OneDrive, make sure you’re logged into Excel with your If you want to save a different version maybe in a
Microsoft account. Whether or not you’re using OneDrive, in different location or with different file name you Save As
Excel most new projects begin the same way - with creating can go toin the backstage view and follow the same steps
or opening a new blank workbook. again you’ll have the option of saving to OneDrive or to this
PC but if you primarily save files to your computer, you may
Opening a new blank workbook want to change the default setting, so this PC is always
• Go to the backstage view and choose New selected to do that click Options in the backstage view then
• If you prefer to start from scratch, click the select save in the left pane.
thumbnail that says Blank Workbook. Now, check the box that reads Save to Computer by
default when you’re done, click OK. If
• You can also start with a template, you’ll find them
you ever forget to save or if your computer crashes while
in the very same place in the backstage view.
you’re working don’t worry the AutoRecovery feature saves a
o A template is a file that comes with its own
backup copy of your workbook automatically to recover an
design and sometimes even complex
unsaved file, all you have to do is reopen Excel and the
formulas or formatting. They make it easy to Document Recovery pane should appear on the left here, you
create professional-looking workbooks can access any recovered versions of the file by default Excel
without a lot of time and effort. auto saves a backup copy every 10 minutes, so if you’re
• You can browse templates that you can see or you working on something for less than 10 minutes, you may not
can search for something specific. be able to use this feature.
• If you see a template you like, just click to view You can also export your workbooks into an
preview, then click create button. alternate file type by clicking export in the backstage view
Opening an existing Workbook exporting as PDF is a good choice, if you need to send a
• Go back to the backstage view, click Open in the left workbook to someone who doesn’t have Excel this format
pane. lets them view but not edit the workbook using a free
program that anyone can download under change file type,
• If the workbook you’ looking for is something you’ve
you can access several other formats depending on what you
worked on recently, it might be listed under Recent
need for.
Workbooks. (In fact you can PIN certain workbooks,
Example if the person you’re sharing with uses Excel
so you can have access to your most frequently used
2003 or earlier you’ll need to send them an Excel 97 to 2003
files. All you have to do is move your cursor over the
workbook instead to share your with others, click Share
file, then click the pushpin icon. To unpin it click the
button in top right corner next you’ll have to upload your
icon again) workbook to OneDrive so click option associated with your
• If the file you’re looking for hasn’t been opened account. Once it’s uploaded, you can email an invitation for
recently, just look to the other options. others to view or edit the file. There are more ways to share
o E.g. under OneDrive, you can access files that at the bottom of the window like attaching your workbook to
are saved online to your OneDrive account. an email or getting a shareable link if you have Office 365,
Under This PC, you can access files that are you can use the AutoSave feature once you upload your
saved locally. Or you can click Browse, then workbook to OneDrive whenever you make a change, it will
select the workbook you want. automatically save your file, however if you want to disable
• When ready, click Open, and the workbook will this feature, click the button in the top left corner keep it in
appear. mind that if you deactivate auto-saving you’ll need to click
“NOW YOU NOW HOW TO CREATE AND OPEN WORBOOKS Save button every time you want to save with so many
IN EXCEL.” options how you save and share your workbook is up to you
whether it’s exporting you file as a PDF or sharing your
4. Saving and Sharing workbook online.
Many features in Office including Excel are geared
towards saving your files online. This s done through 5. Excel: Cell Basics
OneDrive, an online storage space for your documents and Every worksheet contains thousands of cells where
files that lets you access them even when you’re away from different columns and rows intersect. You will need to know
your computer. If you want to OneDrive, make sure you’re how to work with these cells in order to enter and edit data.
logged in to Excel with your Microsoft account. Every cell has its own name based on its location, sometimes
First, let’s take a look at the regular save Quick Access called a cell address. The address also appears in the name
command on the Toolbar, just click and if it’s a new box above the worksheet. just click it.
workbook you’ll be taken to the backstage view where you You canTo select a cell, even use the
can choose where to save you file for. arrow keys on your keyboard to navigate between, instead of
Now, let’s save it to our computer click the Browse clicking with your
button to choose a location for your workbook then enter a mouse. To select more than one cell at a time
file name and click Save when you’re done. (known as a
cell range), click and drag, and the entire range will be Another way to make your text stand out is to use
highlighted. bold, italic, and underline.
You can enter many things into a cell, like a text, open To fill a color on the header, the menu next to
formulas, the Fill Color command and choose the color you want.
functions, and clear the contents of an extra row You can also use Cell Styles to quickly apply
formatting different formatting in your worksheet. Select the cells then
elements. click the Cell Styles command then mouse over the styles for
You canby selecting the cells then clicking the Clear a preview, and click the style that you want.
command on the ribbon and choosing Clear Adding a fill color to a cell range causes the line in-
Contents. You can also use backspace or between the cells to disappear. You can add some borders
delete on your keyboard. by clicking the drop-down arrow next to the Border command
If you want delete the cells all together to, just then you will find many different options.
click the Delete command To change the placement, or alignment, of your
and it will remove the cells from the worksheet, causing the
text using the options in the Alignment group. An
cells
s addition to left, center, and right, text can also be
To save time by copying and pasting content, aligned to the bottom, middle, or top of the cell.
underneath to shift up and fill in the gap.
click
the cell you want then click Copy on the ribbon and you will 7. UNDERSTANDING NUMBER FORMATS
see a dashed box that appears around the copied cell. Next,
select the cells where you want the content to go then click Excel allows you to type different data on your
Paste. worksheet. Along with text you can use different kind of
To access more paste options, open the drop-down numbers, such as decimals, percentages, dollar amounts,
menu. The commands will come in handy if you are dates and more. And to do these, Excel has a special feature
copying and pasting cells that contain formulas or called “NUMBER FORMATS”.
advance formatting. You can also get to these commands by
right clicking in the worksheet. select NUMBER FORMATS
your To cut and paste, cells then right click It tells excel what exactly type of data you are working
and choose Cut. The original content will disappear as soon on. When you enter data on the excel, it will apply the
as you paste it in a new location. necessary number format automatically.
There is also the drag and drop technique for
moving cells from one place to another. To do that, start by EXAMPLE:
selecting your cells then place your cursor on an outside edge When you type “1/1/15” on a cell, the Number Format
then it will turn into a symbol with four arrows and a pointer. drop down menu on the home tab , you can see that the cell
Next, click, hold, and drag the cells wherever you want then is now using the DATE number format.
to go, then release your mouse and the cells have moved to * When you click the Drop Down menu, you can see
their new location. there are more types of Number Formats.
You canFilling in cells with the fill handle. fill *GENERAL FORMAT is the default number format of
vertically or horizontally by selecting the cell you want to use every cell in the spreadsheet.
then clicking and dragging the square in the bottom right
corner. The fill handle can also be used to continue a series, Using the example date “1/1/15”:
like numbers, dates, and other information that is listed in If you want to modify it and use the “Long Date
sequential order. Format” it will be presented as “ January 1, 2015. It
does not changes the data in the excel, it just changes
how it was displayed.
6. Excel: Formatting Cells
To make your text bigger, go to the Home tab then When using Calendar Date; you can use the “Fill Handle” on
locate the font size box. You can either type in the exact font the lower right corner of the cell to continue the series of
size you want or click the drop-down arrow to select a size dates.
and you will see a preview of the results as you go down the
list. Increase and decrease are two other options for quickly Unfortunately ,Excel does not always understand what we
changing the size of your text. All you have to do is click the type. To be able to understand, Excel requires to enter the
command. data in a specific way.
, clickTo change the font style the drop-down arrow
TYPE OF WRONG RIGHT
on the font style and you will get a preview as you make your
DATA
way down the list.
click theTo change the font color, arrow next to the DATE January 1st 1/1/2015
font color command then you can choose one of the theme PERCENT 50 50%
colors or select More Colors. TIME Noon 12:00 pm
PERCENTAGES: 1. Locate and click the Select All button just below the
When you type a number followed by the percent sign name box to select every cell in the worksheet.
(8%) the Percentage Number Format will automatically apply 2. Position the mouse over a row line so the cursor
to that cell. Percentages can also be written as decimal becomes a double arrow.
number. If the data (8%) changed into General Number Click and drag the mouse to increase or decrease the row
Format it will be shown as 0.08. If you type 8 into a cell and height, then release the mouse when you are satisfied. The
apply the Percentage Formatting afterward, it will be shown row height will be changed for the entire worksheet.
as 800%.
To insert rows:
Below the Number Format drop down arrow, there is a 1. Select the row heading below where you want the
small button with a left and right arrow. These commands new row to appear. In this example, we want to
allow you to control how many decimal places are displayed insert a row between rows 4 and 5, so we'll select
in your cell. row 5.
8. Excel: Modifying Columns, Rows, and Cells 2. Click the Insert command on the Home tab.
3. The new row will appear above the selected row.
In Excel every workbook is different, that means you When inserting new rows, columns, or cells, you will see a
may want to modify certain things – like the size and layout paintbrush icon next to the inserted cells. This button allows
of your cells to better fit the data you’re working with. you to choose how Excel formats these cells. By default, Excel
formats inserted rows with the same formatting as the cells
in the row above. To access additional options, hover your
To modify column width: mouse over the icon, then click the drop-down arrow.
In our example below, column C is too narrow to display all of
the content in these cells. We can make all of this content To insert columns:
visible by changing the width of column C. 1. Select the column heading to the right of where you
1. Position the mouse over the column line in the want the new column to appear. For example, if you
column heading so the cursor becomes a double want to insert a column between columns D and E,
arrow. select column E.
2. Click and drag the mouse to increase or decrease 2. Click the Insert command on the Home tab.
the column width. 3. The new column will appear to the left of the
3. Release the mouse. The column width selected column.
will be changed. When inserting rows and columns, make sure to select the
With numerical data, the cell will display pound signs entire row or column by clicking the heading. If you select
(#######) if the column is too narrow. Simply increase the only a cell in the row or column, the Insert command will
column width to make the data visible. only insert a new cell.

AutoFit column width: To delete a row or column:

The AutoFit feature will allow you to set a column's width to It's easy to delete a row or column that you no longer need.
fit its content automatically. It will automatically adjust the In our example we'll delete a row, but you can delete a
width so that it fits the contents of the cells exactly. column the same way.
1. Position the mouse over the column line in the 1. Select the row you want to delete.
column heading so the cursor becomes a double 2. Click the Delete command on the Home tab.
arrow. 3. The selected row will be deleted, and those around
2. Double-click the mouse. The column width will be it will shift, causing the other cells to move and fill in
changed automatically to fit the content. You can also the gap automatically.
AutoFit the width for several columns at the same Just remember that there’s a difference between clearing
time. Simply select the columns you want to AutoFit, and deleting: deleting cells removes them from your
then select the AutoFit Column Width command from worksheet; clearing just removes the contents.
the Format drop-down menu on the Home tab. This
method can also be used for row height.
To move a row or column:
Rows work the same way. You can adjust them manually, or Sometimes you may want to move a column or row to
auto size them to fit the height of your text. Sometimes it is rearrange the content of your worksheet. In our example
useful to be able to resize all of your cells at once. we'll move a column, but you can move a row in the same
To modify all rows or columns: way.
Instead of resizing rows and columns individually, you can 1. Select the desired column heading for the column
modify the height and width of every row and column at the you want to move.
same time. This method allows you to set a uniform size for 2. Click the Cut command on the Home tab, or press
every row and column in your worksheet. In our example, we Ctrl+X on your keyboard.
will set a uniform row height. 3. Select the column heading to the right of where you
want to move the column. For example, if you want
 to move a column between columns E and F, select There is a downside to using the Merge command. If you
column F. want merge multiple cells each containing data, Merge will
4. Click the Insert command on the Home tab, then only keep the contents of the upper left cells and dicard
select Insert Cut Cells from the drop-down menu. everything else.
5. The column will be moved to the selected location,
and the columns around it will shift.
You can also access the Cut and Insert commands by right- 9. Why You Should Avoid Merging
clicking the mouse and selecting the desired Excel has aCells lot of
commands from the drop-down menu. great features
that can make your spreadsheets run more smoothly but
To hide and unhide a row or column: merge cells is not one of them. Merged Cells are notorious
At times, you may want to compare certain rows or columns for creating problems in spreadsheets. You shouldn't use
without changing the organization of your worksheet. To do them unless you absolutely have to and there is an
this, Excel allows you to hide rows and columns as needed. In alternative you can use instead.
our example we'll hide a few columns, but you can hide rows Here is a four cells merged in to one cells, but there's a
in the same way. problem when try to sort this data like sort largest to smallest
1. Select the columns you want to hide, right-click the - it doesn't work or if you want to copy some data from one
mouse, then select Hide from the formatting menu. part of our sheeet and paste it to the merger cell it doesn't
In our example, we'll hide columns C, D, and E. work either it also gives an error message. The problem is
2. The columns will be hidden. The green column line many Excel features rely on the consistent grid of rows and
indicates the location of the hidden columns. colums that every spreadsheet has by default. Merge Cells
3. To unhide the columns, select the columns on both break out of this grid so Excel may not know how to handle it.
sides of the hidden columns. In our example, we'll There is another way to get the same basic look without
select columns B and F. Then right-click the mouse merging cells.
and select Unhide from the formatting menu. 1. Unmerge the merged cells.
2. With all four cells selected click the launch button on
Wrapping text and merging cells the alignment group.
Wrapping Text which is one way of addressing cells that 3. In the Horizontal Menu, select center across
contain more text than they can actually display. selection and click okay.
To wrap text in cells: Now you won't have a problems. It's worth noting that center
1. Select the cells you want to wrap. In this example, across selection only works horizontally so for vertical groups
we'll select the cells in column C. of cells you may still want to merge.
2. Click the Wrap Text command on the Home tab. Use Center Acrros Selection as much as possible because it
3. The text in the selected cells will be wrapped. will help you avoid all of the problems associated with
Click the Wrap Text command again to unwrap the text. merged cells.

To merge cells using the Merge & Center command:

10: TABLE
1. Select the cell range you want to merge.
Tables are a great way to add more advanced formatting to
2. Click the Merge & Center command on the Home
the worksheets, like banded rows, preset color schemed, and
tab.
other design elements
3. The selected cells will be merged, and the text will
- Includes filtering by default, which let sort the data, or
be centered.
narrow it down depending on what needed. To create a
table: Select cells that will be included and click the Format as
To access additional merge options:
Table command on Home tab, and will show several different
If you click the drop-down arrow next to the Merge & Center
styles to choose.
command on the Home tab, the Merge drop-down menu will
If it has to be special formatting in the header row, make the
appear.
box in format as table checked.
Filtering data: By using the drop-down arrows that appears
on the table
From here, you can choose to:
Fitting more content into the table: The Excel gives 2 ways to
This Merge & Center: merges the selected cells into add more columns or rows in the same style as the rest of the
one cell and centers the text. table. Just enter the data in any adjacent column or row, and
This Merge Across: merges the selected cells into it will be roped into the table automatically.
larger cells while keeping each : Click, Hold and Drag the bottom right corner to
row separate. create any size of table needed.
This Merge Cells: merges the selected cells into one cell Design tab: You’ll find everything that is needed in designing
but does not center the text. the table
This Unmerge unmerges selected cells.
Cells:
Table Style group: Changing the table style once added one have control over like what parts of the worksheet to print
to worksheet, and get preview Benefits of using table in and more.
Excel: Backstage view is where you can find everything you
• Make it easy to change the look and feel of the need.
worksheet 1. click the print pane.
• Lot faster than formatting the cells -where you can adjust your settings, see a preview
Customizing the table: Add special formatting to the last and print your workbook all in one place.
column or total row. And it will look slightly different 2. adjust the number of copies you want.
depending on the table style 3. then click the print button right away.
If you want the range to be no longer a table, click Convert to • If your workbook contains multiple sheets,
Range on the Design Tab and click Yes to confirm you’ll need to do is decide whether to print
Options on the Home Tab: Formatting manually the table only the active sheets which is the default
Clear Command to restart formatting from scratch. print method or the entire workbook.
• You can also narrow it down to just your
11: CHARTS current selection.
Charts are great tool for presenting numerical data and also WORKSHEET is considered active when it’s selected like the
enhancing the worksheets in Excel. central sheet is selected now to print more than one
Column Charts are good all-around charts and they work worksheet but not the entire workbook just HOLD CONTROL
with many different types of data Line Charts are ON YOUR KEYBOARD WHILE YOU SELECT THE SHEETS YOU
best for showing trends over time WANT, JUST HOLD CTRL ON YOUR KEYBOARD WHILE YOU
Pie charts are unique because they let you see data in SELECT THE SHEETS YOU WANT.
proportion, instead on a graph IF YOU DON’T WANT TO PRINT THE WHOLE WORKSHEET
Bar chart which is basically just a column chart turned on its 1. start by selecting the data you want including your header
side row if you have one.
Area charts, which are similar to line chart, except the areas 2. navigate back to the print pane in the backstage view.
under the lines are filled in. 3. click Print Selection.
Create a chart: Select data including any header that wants AS AN ALTERNATIVE TO WHAT HAS MENTIONED ABOVE,
to be used. The go to Insert tab, and look to the commands in 1. you can create a permanent selection using the print area
the Chart group command (which can be found in the Page Layout tab)
Below the chart, there’s a type of key called a legend, and 2. choose your data
this tells us the color represented. 3. set print are from the menu
To group or rearrange the data: Select the chart then click 4. choose clear to remove it if you have changed your mind.
Switch Row/ Column on the Design tab PAGE ORIENTATION (under print pane in the backstage
To try different chart all together, click the Change Chart Type view)- so you can fit more rows, change its orientation to
command on the far right of the tab, then pick something landscape.
else from the dialog box Another option is to go to Scaling and choose Fit All
Change the chart layout: Go to the Quick Layout command Columns on One Page, this will automatically shrink your
and make a selection from the menu printout so every column fits on a single sheet of paper.
Making further adjustments: Add Chart Element command : PRINT TITLES- through this, you can repeat important data
Chart Elements shortcut (Found on the button with the plus like the header row.
sign, when chart is selected). Add or remove elements from 1. go to Page Layout tab
the chart Click the arrow to the right to access even more 2. click the Print Titles command
options To edit one of chart elements: You have to select the 3. click the button beside the range you want to repeat
placeholder then replace the text
(choose either rows or columns)
Note: When inserting a chart, it appears the same sheet as -the dialog box will collapse and the cursor will turn
the source data appears by into a selection arrow.
default 4. click OK.
Moving it to a different worksheet: If you want to divide your data evenly when printing,
1. Chart is selected then click the 1. switch to page break preview
Move Chart command mode using the button in the bottom right
2. Select the sheets where you want the chart to go corner. This makes easier to see your page
3. Click ok. And the chart appears whatever location breaks so you can adjust them as needed.
you choose 2. click and drag the blue dotted line
3. then release where you want the
page break to go.
12. Page Layout and Printing
Workbooks can be used completely electronically,
but sometimes you need to print them too. It’s important to
understand the basics of printing plus the other things you