Create and Learn Excel in One Day

www.EngineeringBooksPdf.
com

Create and Learn Excel in 1 day
Learn Ms Excel in one day by creating a Budget Control.

Roger F. Silva
www.EngineeringBooksPdf.com
Table of Contents
Introduction
1. Creating the Budget Control Sheet
2. Formatting your Budget Control Sheet
3. Upside Downside Sheet
4. Creating Charts
5. Printing worksheet
6. Video URL

Introduction

Dear Reader,

I have used Microsoft Excel professionally for 15years, teaching and working in
multinational companies.
During this period hundreds of people came to me for help, asked questions and
complained about how difficult it was to learn Excel. Whilst looking further into the
matter, I observed that the courses and books available were very long, tiring and
expensive. People had difficulty learning the ” Real-World Excel.”
This book is designed to teach you how to build a simplified budget control sheet using
important and useful functions, Excel tools and concepts in less than 24hours. I will not go
into deep theories as the purpose of this book is to Create and Learn.
You will follow step-by-step instructions on the creation of a budget control sheet, rapidly
increasing your knowledge. If doubts arise, you can watch the video where I build this
budget control sheet by following the steps in this book.
If you want to expand your knowledge of the wonderful tool that is Ms Excel, check out
my other publications that are focused on market needs and fast learning.

Thank you for Creating and Learning.

Roger F. Silva
rogerfsilva.com

Budget Control
1. Creating the Budget Control Sheet

A budget is telling your money where to go
instead of wondering where it went.
-Dave Ramsey, American financial author-

In this chapter you will create a budget control sheet and will learn how to set a table,
enter data, insert formulas and use autofill. If you have any questions throughout the book
you may use the step-by-step video available on the internet (see the video URL on the
last chapter of this book).
Example 1

1. Let’s start by finding the Excel application and opening it.

2. At the desktop environment click on the option “Blank workbook”.

3. Your file will already be named, possibly as “Book1”.

4. Rename it by clicking on “File” and then the left option “Save”.

5. Excel will show you many options to save your file. Choose “Browse” option.

6. The “Save popup” will appear and you can select the location you wish to save the
file. Type the name Create and Learn – Budget Control.xlsx and click “OK” or press
“Enter” in your keyboard.

7. Click in cell A3 (Column A, Row 3) and type the word Total then press “Enter”.

8. Type Budget in cell B1 then press “Enter”, type January in cell B3 and press
“Enter”.

9. You will save your time by using the “Auto Fill” feature. In cell B3 move your
mouse to the little handle in the bottom-right corner, the mouse handle will turn into a
black cross (see picture below).

10. Click and drag to cell M3 and then release your mouse button.

11. Type Income 1 in cell A6 and Income 2 in cell A7.

12. Type Expense 1 in cell A9.

13. Use the handle in the bottom-right corner of cell A9 to save time by clicking and
dragging until cell A16, then releasing the mouse button.

14. Now type the values in the table like the image bellow.

15. You can save time by using the “Copy” and “Paste” functions. Select (click) cell
B10 and hold the “Shift” key, then select B16 and release the “Shift” key.

16. The cells will be selected and you should now click on “Copy”.

17. Click in cell C10, hold the “Shift” and click cell M16. Note that the whole range is
selected.

18. Click on “Paste” and you will see that all of the ranges selected will now have the
same values.

19. Type the other values that are in the figure bellow.

20. Now let’s create your first formula that will sum all the incomes and expenses for
each month. Type =SUM(B6:B16) in cell B4 and press “Enter”. You will see the result of
the sum on the range selected.

21. Type =SUM(C6:C16) in cell C4 and press “Enter”. You will see the result of the
sum on the range selected.

22. Now you can see that there is a pattern and you can use the “Auto Fill” feature.
Click your mouse on the bottom right handle of cell C4 and drag until cell M4, then
release the mouse button. All months will have the balance, note that some values are
negative.

23. Now you need the total of the year. Type =SUM(B4:M4) in cell A4 and press
“Enter”.

24. Click on save or press Ctrl+S to save your document.

Congratulations! Now your Spreadsheet is functionally complete!

2. Formatting your Budget Control Sheet

You can have data without information,
but you cannot have information without data.
-Daniel Keys Moran, Programmer and science fiction writer-

In this chapter you will format your budget control sheet with ease. You will learn how to
format cells and text, change fonts, use zoom, format tables and use conditional
formatting.

Example 2

25. Select cell A4, go to the “Home Tab” and in “Number Group” click on “Coma
Style”. This will format your number with a thousand separator then you can show more
or fewer separators by clicking on “Increase decimals” or “decrease decimals”.

26. Select range B4:M16: Select B4, hold Shift, select M16 and release the Shift.

27. With the range selected, go to the “Home Tab” and in “Number Group” click on
“Coma Style” and “Increase decimals” or “decrease decimals” to make your range look
like figure Example 2.

28. Select range A1:M4 and with the range selected go to the “Home Tab” and in “Font
Group” click on “Bold” to make the text bold.

29. Select cell B1 and go to the “Home Tab”, then “Font Group” and click on “Size”
and select size 20 for your text.

30. Select range A4:M4 and use the same sequence to change the size of the range to
20.

31. Go to the “Page Layout Tab”, then “Sheet Options Group” and uncheck “Gridlines
View”.

32. Now click the right button on column A, select the “Column Width” option,
configure the width to 18 and click “OK”.

33. Select Column B:M (select column B, hold Shift, select column M and release the
Shift), click right button inside the range, choose the “Column Width” option and then
configure the width to 15.4 and click “OK”.

34. Go to the “View Tab”, “Zoom group” and click on “Zoon”. Type 85 in the
“Custom” option.
35. Select range A3:M3 and with the range selected go to the “Home Tab”, “Alignment
group” and click on “Center”, and this will center your text.

36. Select cell A4 and go to the “Home Tab”, “Font group” and then click on “Fill
Color” select “White, background 1, Darker 15%” color and you will fill the background
of the selected cell.
*You can use different colors if you want or are using a different Excel version.

37. Ranges A6:A7 and A9:A16 will have the same color. You just have to select the
range and choose the background color.

38. Select range B3:M3 and choose “Green, Accent 6, Darker 50%” color.

39. To select alternate cells, you will use the “Ctrl” key instead “Shift” Key. Hold the
“Ctrl” key and select cells B4, D4, F4, H4, J4 and L4. Color the selection with “Green,
Accent 6” color.

40. Hold the “Ctrl” key and select cells C4, E4, G4, I4, K4 and M4. Color the selection
with “Green, Accent 6, lighter 40%”.

41. Select range B1:M1 and go to the “Home Tab”, “Alignment group” and click on
“Merge & Center”. Your range will be merged into one cell.

42. Select B1 (you will automatically be selecting the range) and color the cell with
“Grey -25%, Background 2, Darker 50%”.

43. Go to the “Home Tab”, “Alignment group” and click on “Align Left” and the word
Budget will be aligned to the left.

44. Select range B1:M4 and go to the “Home Tab”, “Font group” and click on “Font
Color” then choose “White, Background 1”.

45. Select range A6:M7 and go to the “Home Tab”, “Font group”, click on “Border”
and select “All borders”. Your range will now have black default borders.

46. Now select range A9:M16 and go to the “Home Tab”, “Font group”, click on
“Border” and select “All borders”.

47. Select range B18:M18 and color the range with “Gray -25%, Background 2, Darker
50%”.

48. Click on Row 2 with the Right button and select “Row Height”, change the number
to 6 and click “OK”.

49. Now do the same to Row 5, Row 8 and Row 17 by clicking the Right button on the
Row.

50. Click on Row 1 with the Right button and select Row Height, change the number

51. Click on Row 4 with the Right button and select Row Height, change the number

52. Select range A4:M4 and go to “Home Tab”, “Styles group”, click on “Conditional
Formatting” and choose “New Rule”.

53. A “New Formatting Rule” Popup will appear, choose “Format only cells that
contains”. Set the options as shown in Figure below Cell Value / Less than / 0.

54. Click on “Format”, choose “Font”, “Color”, “Dark Red”, click “OK” and “OK”.
All cells selected with values less than 0 will now be red .

55. Double click the name of the Sheet (Sheet1) and type a new name Budget Control.

56. Click on save or press Ctrl+S to save your document.

Congratulations! You have now created and formatted your Budget Control!
3. Upside Downside Sheet

The most reliable way to forecast the future is to try to understand the present.
-John Naisbitt, American author of futures studies-

In this chapter you will create a small Upside (Possibility of gain) and Downside (risk of
loos) table where you will use the previous concepts of creating and formatting and will
practice the use of formulas .
Example 3

57. To create a new sheet, you just need to click on “New Sheet”.

58. Double click on “Sheet 1” to edit the name. Type Upside Downside and press
“Enter”.

59. On this new sheet select B1 and type Impact, in C1 type Total, in D1 type %Actual,
in A3 type Upside +20% and in A4 type Downside -30%.

60. In A1 you will link the accumulated budget value to our new sheet. Type =‘Budget
Control’!A4, in A1. In other words, A1 will be “equal” the address ‘Budget Control’!A4.

61. In B3 type =A1*20% and press “Enter”. The * (asterisk) is for multiplication.

62. In B4 type =A1*30% and press “Enter”.

63. In C3 type =A1+B3 and press “Enter”. The +(plus sign) is for addition.

64. In C4 type =A1-B4 and press “Enter”. The –(minus sign) is for subtraction.

65. In D3 type =C3/A1 and press “Enter”. The / (forward slash) is for division.

66. In D4 type =C4/A1 and press “Enter”.

* Note that the “Impact” column shows how much +20% or -30% will represent in the
total balance. The “Total” column shows the result of the addition or subtraction. And “%
Actual” column shows the percent of the possible total, comparing to the actual balance.

67. Now select A1 and go to the “Home Tab” and in “Number Group” click on “Coma
Style” and “Increase decimals” or “decrease decimals” to make your cell look like
Example 3.

68. Select range B3:C4 and go to the “Home Tab” and in “Number Group” click on
“Coma Style” and “Increase decimals” or “decrease decimals” to make your range look
like Example 3.

69. Select range D3:D4 and go to the “Home Tab” and in “Number Group” click on
“Percent Style” and “Increase decimals” or “decrease decimals” to make your range look
like Example 3.

70. Select range A3:D4 and go to the “Home Tab”, “Font group”, click on “Border”
and select “All borders”.

71. Select range B1:D1 and go to the “Home Tab”, “Font group” and click on “Fill
Color” select “Gray -25%, Background 2, Darker 50%” color.

72. With the range selected, go to the “Home Tab”, “Font group” and click on “Font
Color” then choose “White, Background 1”. Make your text bold by clicking “Bold”.

73. Select range A3:A4 and click on “Bold”.

74. Go to the “Page Layout Tab”, “Sheet Options Group” and uncheck “Gridlines
View”.

Congratulations! You have now created and formatted your Upside Downside sheet!
4. Creating Charts

The goal is to turn data into information,
and information into insight.
– Carly Fiorina, former CEO, Hewlett-Packard Co-

In this chapter you will create charts to give life to your data, representing them visually.
You will learn how to create graphics, format headings and change layout.
Example 4

75. Select “Budget Control” sheet

76. Select range B3:M4 and go to “Insert Tab”, “Charts group”, “Insert Area or Line
Chart”, “2-D Line” and click on “Line”. You will create a line chart line.

77. Click and Drag the chart close to cell A20 and release the mouse button.

78. Select range B3:M4 again and go to “Insert Tab”, “Charts group”, “Insert
Waterfall” and click on “Waterfall”.

* Note that you can choose any chart like “Column Bar” and “Pie” for example. If your
Excel version does not have the “Waterfall” option, you can choose “Column Bar 2-D
Clustered” for training.

79. Drag your chart close to cell H20.

80. Click on the Line Chart title to change the name and type Line Chart - Monthly
Balance

81. Click on Waterfall Line title (or the other chart you chose) to change the name and
type Waterfall Chart - Total Balance.

82. Select the Line Chart and go to “Format Tab”, “Size group” and on “Shape Height”
type 10 cm and on “Shape Width” type 18 cm.

83. Select the other chart and go to “Format Tab”, “Size group” and on “Shape Height”
type 10 cm and on “Shape Width” type 18 cm.

84. With the chart selected go to “Design Tab”, “Chart Styles group” and if you want
you can change your chart styles in an easy way.

Congratulations! You have now created a simple but efficient Budget Control, have
formatted and shown the results through beautiful charts. Now it’s time to print!

5. Printing worksheet

“Words, once they are printed, have a life of their own.”
– Carol Burnett, American actress, comedian, singer, and writer-

Example 5

85. Go to “File Tab”, “Print option”. You will see many options to configure your
printing.

86. Type 1 in the field “copies”, to set the number of copies you want.

87. Select “Microsoft Print to PDF” or any printer you want.

88. Select “Landscape Orientation”.

89. Select “Letter”, to set the paper.

90. Select “Narrow Margins”.

91. Select “Fit Sheet on One page”

92. Look the example in the right and if it is ok (and if you want) click print.

Congratulations! You have finished your Budget Control Sheet step-by-step!

Dear reader.
A good rating and your positive review are incredibly important for me. If you have any
comments or suggestion, please send me an email and I will be more than happy to hear
from you.

I hope you have achieved your goal to learn more about Microsoft Excel quickly and
creating a beautiful and useful tool.
For its constant evolution try my other books in the Create and Learn series and go
evolving rapidly, create new tools and learn new practices in Microsoft Excel.

Thank you for the time we spent Creating and Learning.
Roger F. Silva
6. Video URL

If you want, you can check out the video made specifically to help you with any doubts:
1. YouTube url: https://youtu.be/wcL64x3creE?list=PLh3N2r-
_o3nfqIBKX7s5X2V3S28nKdPjr
2. YouTube channel: Roger F Silva
3. Website: rogerfsilva.com

Create and Learn Excel in One Day

Uploaded by

Copyright:

Available Formats

Create and Learn Excel in One Day

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Create and Learn Excel in One Day

Uploaded by

Copyright:

Available Formats

www.EngineeringBooksPdf.

You might also like