ALL-IN-ONE EXCEL 2022

BIBLE FOR DUMMIES

(BOOK 2)

Step-By-Step Tutorial To Master

Microsoft Excel From Scratch And
Quickly Become A Pro

Bryant Shelton
Copyright 2022 Bryant Shelton

All Rights Reserved

 Table Of Contents

INTRODUCTION
WHAT IS THE BEST WAY TO OPEN MICROSOFT EXCEL?
THE EXCEL FORMULA BAR: WHAT IS IT?
HOW TO DISPLAY THE FORMULA BAR (OR HIDE IT)
RESIZE THE FORMULA BAR
USING THE FORMULA BAR TO ENTER DATA
USING THE FORMULA BAR TO ENTER A FUNCTION
UTILIZE NAME BOX
SHAPE EDITING FROM THE FORMULA BAR
EXCEL GRIDLINES (REMOVE, SHORTCUT, PRINT,
& CHANGE COLOR)
THE KEYBOARD SHORTCUT FOR EXCEL’S GRIDLINES
GRIDLINE COLOR MODIFICATION
EXCEL BUTTON ADDITION INSTRUCTIONS
USING SHAPES, ADD MACRO BUTTONS
USE FORM CONTROLS TO ADD MACRO BUTTONS
USING ACTIVEX CONTROLS, ADD MACRO BUTTONS
EXCEL HEADER AND FOOTER INSERTION
INSTRUCTIONS
HOW TO ADD A HEADER IN EXCEL
HOW TO ADD A FOOTER IN EXCEL
HOW TO INSERT HEADERS AND FOOTERS WITH PREDEFINED
FORMATS IN EXCEL
FORMAT THE HEADER AND FOOTER BY EDITING THEM
IN EXCEL, HOW DO YOU ADD A PAGE NUMBER?
UTILIZING THE PAGE SETUP DIALOG BOX, ADD PAGE NUMBERS
SETTING A DIFFERENT STARTING PAGE NUMBER
 ADD ALTERNATIVE PAGE STYLES FOR EVEN AND ODD PAGES
USING THE HEADER AND FOOTER, DELETE THE PAGE NUMBERS
USING THE PAGE SETUP DIALOG BOX, REMOVE THE PAGE
NUMBERS
HOW TO INSERT STRIKETHROUGH [SHORTCUT + COMMAND] IN
EXCEL
HOW TO CHANGE NEGATIVE NUMBERS IN EXCEL TO POSITIVE
ORGANIZING WORKSHEETS IN EXCEL
HOW TO GROUP EXCEL WORKSHEETS
ALL WORKSHEETS COMBINED
SEPARATE WORKSHEETS
HOW TO MAKE BLANK CELLS IN EXCEL STAND
OUT
EXCEL TIMESTAMP INSERTION (FORMULA + VBA
+ SHORTCUT)
EXCEL BULLET POINT INSERTION TECHNIQUES
CONCLUSION
 Introduction
Firstly, I’ll begin by saying if you’ve not gotten book one of
this series by the same author, you should do so and start from
there because it’ll lay a solid foundation and help you
understand whatever you’ll be seeing in this book two.

The truth is that before attending a job interview, you must

have a fundamental understanding of Microsoft Excel.
Microsoft Excel is used by everyone from accountants to
receptionists and human resources to administrative
departments.

It isn’t just a problem for big businesses; small business

owners and college students use it daily. That is something you
must not overlook. Learning fundamental Excel activities (at
least some) is necessary in today’s times if you want to get a
job.

And I’ve put together this guide to assist you. Using examples,
this guide will help you learn all of the fundamentals. Also
included are some of the essential beginner’s tutorials.

So, without further ado, let’s get right to work.

There are several spreadsheet programs available, but Excel is

the most popular. It has been in use for the past 30 years, and
over that time, it has been improved to include more and more
capabilities. Microsoft Excel is a spreadsheet program that
may store and analyze numerical and statistical data. Microsoft
Excel has various tools for doing tasks such as computations,
pivot tables, graphing tools, macro programming, and more. It
works with different operating systems, including Windows,
Mac OS X, Android, and iOS.

Microsoft Excel is a part of the Microsoft Office suite of

programs. It’s a multi-row, multi-column electronic
spreadsheet used to organize, visualize data(s), and do various
calculations. Each row and column comprises a cell containing
1048576 rows and 16383 columns. The column name and row
number indicate the address of each cell, such as A1, D2, and
so on. Another name for this is a cell reference.

A table is created by a collection of columns and rows in an

Excel spreadsheet. Columns are typically assigned
alphabetical letters, and rows are usually assigned numerals.
The convergence of a column and a row is known as a cell. A
cell’s address is determined by the letter representing the
column and the number representing the row.

To some extent, we all work with numbers. We all have

monthly expenses that we must cover with our wages. To
spend appropriately, one must first understand their income
and expenditure. When we need to record, analyze, and save
numeric data, Microsoft Excel comes in helpful.

Microsoft Excel is available in various formats. It can be

available from a computer hardware store that also offers
software. Since Microsoft Excel is an application that is part of
the Microsoft Office suite, you may also get it from the
Microsoft website. However, you’ll have to pay for the license
key.

Excel’s greatest strength is that it can be used for a wide range

of business operations, including statistics, finance, data
management, forecasting, analysis, inventory and billing
tracking, and business intelligence. The following are some of
the things it can perform for you:
Templates/Dashboards
Automation of Tasks
Store and Import Data
Manipulating Text
Number Crunching
Charts and Graphs
And Much More
 What Is The Best Way To Open
Microsoft Excel?
Excel may be used just like any other Windows program. Take
the steps below if you’re using Windows with a graphical user
interface (such as Windows XP, Vista, or 7);

Select the Start menu from the menu bar.

Select all programs.
Select Microsoft Excel.
If it has been added to the start menu, you can also open it
from there. If you’ve made a desktop shortcut, you can also
use it to open it. To run Excel on Windows 8.1, follow the
steps below;

Select the Start menu from the menu bar.

Look for Excel
Select Microsoft Excel.

The three most critical Excel components to comprehend first

are:

Cell: A spreadsheet’s most minor but most potent component

is the cell. You have the option of typing or copy-pasting your
data into a cell. Data can be anything from a letter to a number
to date. Its size, font color, background color, borders, and
other aspects can all be changed.
Every cell is identifiable by its address, including the column
and row numbers (If a cell is on the 11th row and column AB,
its address will be AB11).
Worksheet: A worksheet consists of individual cells, each of
which can have a value, a formula, or text. It also contains a
drawing layer that holds charts, photos, and diagrams but is
not visible. When you click the tab at the bottom of the
workbook window, you can access each worksheet in the
workbook. A workbook can also store chart sheets, which
display a single chart and are accessed by clicking a tab.

Workbook: Like any other application, a workbook is a

separate file. There are one or more worksheets in each
workbook. A workbook can also refer to a collection of many
worksheets or a single worksheet.

You can delete or add worksheets, hide them without

removing them, and rearrange the order in which they appear
in the workbook.

All these and much more are already covered in book one of
this series, so if you’ve not gotten it yet, do well to begin from
book one by the same author, as it’ll lay a solid foundation and
help you understand whatever you’ll be seeing in this book
two.
 The Excel Formula Bar: What
Is It?
The Excel Formula Bar is a little bar that sits below the ribbon.
It shows the content of the currently selected cell on the right
side and the cell address on the left. The formula bar also
allows you to insert a value directly into the cell. There are
three buttons (Enter, Cancel, and Insert Function).

1) Title Box

2) Expand horizontally

3) Buttons

4) Entry bar

5) Expand vertically
 How to Display the Formula Bar
(or Hide it)

The view tab can make the formula bar visible if it is hidden.
The formula bar is active by default.

Navigate to the view tab, select the show group and tick the
formula bar boxes.

In addition to this, you can enable it using Excel’s settings.

Visit the Excel menu. Click on show the formula bar under
Advanced > Display.
If you want to conceal it, you can follow the same procedures.
 Resize the Formula Bar

The formula bar is small by default, but you can widen it

slightly. When your cursor hovers over the formula bar’s
bottom, it transforms into a vertical arrow with two ends,
which you can draw down to widen the formula bar.

You may also use the shortcut key Control + Shift + U to

expand the formula bar vertically. The drop-down icon on the
right side is another option.

Additionally, you can adjust its width by stretching it to the

right or left by placing your cursor over the three dots that are
situated between the name box and formula bar.
 Using The Formula Bar To Enter
Data

By selecting the cell and clicking on the input bar in the

formula bar, data can be entered into a cell using the formula
bar.

Once you have finished entering the value you desire, click the
enter button, which can be found on the left side of the
formula bar.

You can also use the escape key or a button to stop entering
data.
 Using The Formula Bar To Enter
A Function

The insert function button is located on the formula bar, and

clicking it brings up a dialogue window where you may select
and insert the function.

When you click OK after choosing the function you wish to

insert, a dialog box allowing you to specify the function’s
arguments will appear.
 Utilize Name Box

You have a name box that displays the cell address for the
chosen cell on the right side of the formula bar. However, you
can navigate by using this name field as well. You represent a
particular cell or a range. When you click on the name box,
you may update it. You can also input the address of a cell,
and when you do so and press enter, you will be redirected to
that cell.

Similarly, you can choose a range by entering its address.

 Shape Editing from the Formula
Bar

You can alter the formula from the formula bar to connect a
shape with a cell.

Choose the worksheet shape that you want to use.

To edit the formula bar, click on it.

Enter “=” and then pick the cell you want to connect to the
shape from the list.

Finally, click OK.

 Excel Gridlines (Remove,
Shortcut, Print, & Change
Color)
When you open a worksheet in Excel, you may see gridlines
(lines of a light gray tint) that make the cells visible. We will
learn how to work with these gridlines in this tutorial.

Excel gridlines can be removed

The “View Tab” is where you need to be in the ribbon.

Go to the “Show” group on it from there.

The gridline checkbox is the next item.

To remove the gridlines from the workbook, uncheck the
option.

You only need to check the box once more to make the
gridlines visible again, but you can also conceal and show
them by selecting “Page Layout” from the gridlines set of
options on the tab.

Additionally, you can discover another method in the Excel

settings. Access Excel Options > Advanced > Display Options
for the Worksheets from the File tab. Turn off “Show
Gridlines.”
Removing gridlines using any of the methods mentioned
above will only affect the currently active worksheet; it won’t
affect the entire workbook. However, you can choose a set of
worksheets and then uncheck the gridline option if you want to
add or remove gridlines from numerous worksheets.
 The Keyboard Shortcut For
Excel’s Gridlines

The following shortcut key allows you to hide and similarly

expose gridlines. The choice from the “View” tab is used by
the following shortcut key.

Alt, W, V, and G

In Excel, print gridlines.

The gridlines won’t appear on the worksheet printout when

you take a printout. However, you turn on the gridlines
printing.

Navigate to the “Sheets Options” group under the “Page

Layout” tab.

The options gridlines can then be found there.

The second checkbox, “Print,” should then be selected.

Note: Excel will only print the gridlines for the used range
when you activate the gridlines printing feature (cells where
you have content). similar to the following
 Gridline Color Modification

Gridlines are light grey by default, but you can change their
hue using Excel’s settings. Select the Gridline Color under the
File Tab, Excel Options, Advanced, and Display Options for
the Worksheets.

If you are using color printing, changing the gridlines’ color

also affects the gridlines’ color on the printed.
 Excel Button Addition
Instructions
Users can add macro-enabled buttons to worksheets in Excel,
and then by clicking on those buttons, macros can be
launched.

By just clicking on the buttons, users may utilize these macro-

enabled buttons to complete a variety of operations like data
filtering, data selection, printing a worksheet, running
formulas, and calculations.

It is simpler to add buttons and integrate macros into them.

The macro-enabled buttons can be added to the worksheet in
Excel using various methods.

You may add the macro buttons to Excel using the quick and
straightforward methods listed below.
 Using Shapes, Add Macro Buttons

Excel shapes can be used to create buttons for users.

Compared to buttons made from Control or ActiveX, buttons
made using shapes provide greater formatting possibilities.

Users can alter shape-based buttons’ designs, colors, fonts, and

styles.

Proceed to the “Insert” tab, click the “Illustrations” icon,

choose “Shapes” from the menu, and then choose any
rectangular button.

After that, on the worksheet, draw a rectangular button using a

mouse.
Now double-click the button and type the text into the field
provided.

You can format the button several ways by selecting the

“Shape Format” tab.

The font style, color, button color, button effects, and more
may all be formatted from here.

Simply right-click the button to bring up a pop-up menu with

several options, allowing you to alter the text, add a hyperlink,
or add a macro.
From this point, you can change the wording, add a hyperlink,
and change the button’s macro.

The macro can now be added to the button by choosing the

“Assign Macro” option.

When you choose the “Assign Macro” option, the “Assign

Macro” dialogue box will be displayed.

Click OK after selecting the macro from this point.

Now that the button has been made, macro-enabled moving
the cursor over it causes the hand point cursor to appear.

Right-click the button, choose “Format Shape,” and then select

the option “Don’t move or size with cells” to stop the button
from moving.
 Use Form Controls To Add Macro
Buttons

First, select the “Developer” tab, then on the ribbon, click the
“Insert” button in the “Control” group.

Then, choose “Form Controls” from the menu and draw a

button on the worksheet using the first button choice.

Click OK after choosing or entering the macro name in the

“Assign Macro” dialogue box.

Clicking Cancel will allow you to add the macro later if you
haven’t built one yet.
To add the macro to the button if it hasn’t already been
assigned, right-click on it from this point and choose “Assign
macro.”

Choose “Format Control” to format the button’s font, style,

color, etc.
Once you click “Format Control,” the “Format Control”
window will open, allowing you to format the button font.

Choose the “Properties” tab, choose the “Don’t move or size

with cells” option, and then click OK to stop the button from
moving.
 Using ActiveX Controls, Add
Macro Buttons

First, select the “Developer” tab, then on the ribbon, click the
“Insert” button in the “Control” group.

Then, choose the “ActiveX Controls” menu’s first button

option and draw a button on the worksheet.

Click the “View Code” icon to open the VBA editor, then
create and add the macro to the button.
Choose “Click” from the drop-down box on the right side of
the editor after selecting “CommandButton1” on the
subprocedure.
 Excel Header And Footer
Insertion Instructions
You may occasionally need to print some basic information
with your spreadsheet data, such as page numbers, the date it
was printed, the name of the workbook, etc. You use Excel’s
Header and Footer feature in relation to your spreadsheet.

You can customize the Excel default Headers and Footers or

use them.
 How To Add A Header In Excel

Go to the “Insert tab” first.

Then select “Header & Footer” from the list of options under
the “Text” group.

When you select “Header & Footer,” Excel shows you where
to add the header items for the current worksheet.

After finishing the header, press anywhere on the sheet to exit

the header section and make your header active.
 How To Add A Footer In Excel

Go to the “Insert tab” first.

Then select “Header & Footer” from the list of options under
the “Text” group.

You can now just click the “Go to Footer” icon found in the
navigation group of the ribbon.

Excel then shows the footer area for the current worksheet
where the footer items can be added once you click the “Go to
Footer” icon.
After finishing the footer, simply press anywhere on the sheet
to exit the footer area and make your footer active.

You will always have three (right, middle, and left) boxes in
“Header and Footer,” where you may enter the text, values,
images, etc., for your header and footer content. You can enter
the information in that box based on which side you want the
header and footer items to appear on.
 How To Insert Headers And
Footers With Predefined Formats
In Excel

Excel has several “Headers and Footers” preconfigured

elements that can be used for your document with a single
mouse click.

Go to the “Insert” tab first.

Then select “Header & Footer” from the list of options under
the “Text” group.

The new “Header & Footer” tab with numerous preconfigured

items appears as soon as you select the “Header & Footer”
option.
Now, you can select any of them you wish to utilize as your
header.

For instance, to use the “Number of Pages” element, simply

click on it; a header will appear inside your spreadsheet.
By clicking on the “Header” and “Footer” options, you may
also choose the predefined “Header and Footer” elements.

You can select from a choice of preconfigured items once you

click “Header” and “Footer.”

After choosing any predefined element, you can view your

“Header” and “Footer” choices by clicking anywhere in the
sheet. This will take you back out of the header and footer
section, where you can see the header and footer that you have
chosen.
Format The Header And Footer By
Editing Them

According to your preferences, Excel allows you to format and

alter the “Header & Footer font” size, style, color, and effect.

First, click on the icon for the page setup dialogue box on the
“Page Layout” Tab.

The “Custom Header” and “Custom Footer” options will then

appear when you select the “Header/Footer” option.

Click on it now to update, change, or personalize the “Custom

Header” or “Custom Footer” option.
As soon as you click the font icon, a pop-up menu will appear
where you may modify or choose the font’s size, color, style,
and effects for your “Header & Footer.”
In Excel, remove the header and footer.

Proceed to the “View” tab and select the “Normal” icon to get
rid of the current “Header & Footer.”

By just clicking on the “Normal” symbol in the status bar, you

may also get rid of the “Header & Footer.”
In Excel, How Do You Add A Page
Number?

Excel allows inserting page numbers when printing Excel

worksheets, just like other Microsoft Office programs like
Word or PowerPoint.

In Excel, users can customize and insert page numbers into a

single sheet or several sheets simultaneously.

To add or remove page numbers in Excel, follow the quick and

straightforward procedures we’ve provided.

The header and footer are used to add page numbers.

First, select the “View” tab, and then, in the “Workbook

views” section, click the “Page Layout” icon.

After that, scroll down the sheet and choose (Left, Center, or
Right) a click location within the “Footer” column based on
which side of the page you want the page numbers to appear.
To add page numbers, select the “Design” tab and then click
the “Page number” or “Number of pages” icon.
Alternately, pick the desired page number printing style by
clicking the “Footer” icon.

Once you choose the option to add and print additional pages,
Excel adds those additional pages as codes to the worksheet.

To view the page numbers at the end, click anywhere outside

the “Footer.”
After inserting the page numbers using the Header & Footer
option, select the “normal view” symbol on the Excel sheet’s
status bar to return to the normal mode.
 Utilizing The Page Setup Dialog
Box, Add Page Numbers

The “Page Setup Dialog Box” option allows users to add page
numbers to all the sheets when they have data on numerous
sheets in a workbook and wish to print them in a continuous
series.

Open the workbook first, then choose the sheets you wish to
print (select multiple sheets)

Then click the “page setup” dialogue launcher under the “Page
Layout” menu.

Select the “Page Setup” window’s “Header/Footer” tab at this

point.

Finally, select the page number printing style you wish to

show by clicking the Footer drop-down arrow, then click OK.
Left-click on the “Print Preview” button to view the preview.

After you click OK, page numbers will be assigned to all of

the selected sheets starting with the first sheet and first page.
 Setting A Different Starting Page
Number

Users can adjust the page number starting number in Excel,

which by default starts with page 1.

First, click the “page setup” dialogue launcher on the “Page

Layout” tab.

Click on the “Page” tab in the “Page Setup” window

afterward.

Now input the number you wish to start your first-page

number from in the “First-page number” field, then click OK.
 Add Alternative Page Styles For
Even And Odd Pages

First, select the “View” tab, and then, in the “Workbook

views” section, click the “Page Layout” icon.

Then, scroll down to the “Footer” column on the first sheet,

and click there.

Select “Design” from the menu and click the “Different Odd
and Even Pages” icon.

Finally, decide the page number printing style you wish to use
to show pages with an odd number of pages.
Your page numbers with the printing style you choose for page
numbers have now been added for pages with an odd number
of pages.

Once more, scroll the sheet to page number 2, then pick the
“Footer” column on that page.

Repeat the procedure you just used on the “Footer” column of

the first page for the pages with odd page numbers after that.

Your page numbers are now inserted for pages with an even
number using the page number printing style you chose.

Odd and even numbers will be printed in two distinct styles

depending on the print page number styles you have chosen
once you have finished both the odd and even footers.
 Using The Header And Footer,
Delete The Page Numbers

Open the “Header and Footer” menu first.

Once you’ve added the page number code, scroll down the
Excel sheet and click in the “Footer” column.

Select the complete page number code at this point, then click
remove.
 Using The Page Setup Dialog Box,
Remove The Page Numbers

First, click the “page setup” dialogue launcher on the “Page

Layout” tab.

Then click the “Custom Footer” button under the

“Header/Footer” menu.

If you applied distinct page number styles for odd and even
pages, choose the “Odd Page Footer” or “Even Page Footer” at
this point.
Finally, click OK after selecting the complete page number
code and pressing remove.
 How To Insert Strikethrough
[Shortcut + Command] In Excel

It denotes cutting a line through a cell’s value. On the Home

tab found on the ribbon in Microsoft Word, there is an option
to strike through text. Consequently, applying it is simple.

Excel does not offer a direct method for applying a

strikethrough to a cell. The ribbon has neither a button nor an
option. But that doesn’t imply we can’t accomplish that. In
reality, we have a few different approaches to this. I want to
share 5 techniques for applying strikethrough in Excel with
you in this part;

1. A Shortcut Key for Cell Strikethrough

If you’re pressed for time and don’t want to waste it, a

keyboard shortcut is an ideal solution for you. You can use: to
apply strikethrough to a cell.

Additionally, you can use the shortcut key: if you are using a
Mac.

But here’s the shocking part:

To apply this to a specific section of text in a cell, edit the cell,
select the desired section of the text, and then press the
shortcut key. Additionally, if you want to apply it to several
cells, you must first choose the complete range or a non-
continuous range of cells before using the shortcut.

2. Upgrade QAT to include a strikethrough button

You may be curious. Yes, that’s correct; we can add a

strikethrough button to the QAT, which will only require a
single click each time. The instructions for this one-time setup
are listed below.

Go to File > Options > Quick Access Toolbar first.

Then click on “Commands Not in the Ribbon” under “Choose

commands from.”

Pick “Strikethrough” from the list and include it in the QAT.

Select OK.

Now that you have a QAT icon, you can easily apply
strikethrough.

This button can also be used to apply the change to several

cells and a specific section of a text.

3. Make Use of the Strikethrough Format Option

Although Excel does not have a direct option for

strikethrough, you can get one through the format choices, as I
previously stated. You must carry out this action.
Choose all of the cells you wish to apply it to.

To access the format options, press the shortcut key Ctrl + 1.

Mark the strikethrough option with a checkmark in the font

tab.

Select OK.

It performs similarly to other choices. It can be used on both

the partial text and numerous cells. What’s the best part, you
ask? Through the same, you may also obtain formatting
settings that are not shown anywhere on the ribbon.

4. Apply Strikethrough Using a VBA Code

Here is the code if you wish to utilize a VBA for
strikethrough. Macro codes work like a charm.

Sub addstrikethrough()
Dim rng As Range
For Each rng In Selection
rng
Font
Strikethrough = True
Next rng
End Sub

Use the code shown above to apply strikethrough to particular

cells. Additionally, you may apply it to a shape to make a
button.

5. Apply Strikethrough Using Conditional Formatting

One of the most excellent methods for applying formatting in

an intelligent approach is conditional formatting. Here’s how
to go about it.

Create a check box in the worksheet first.

Link to cell A1 after that, then change the font color of A1 to
white.

Choose cell B1 and click the Home tab, Styles, Conditional

Formatting, and New Rule buttons.

Choose the option that reads “Use a formula to select which

cell to format.”
In the formula input box, type =IF(A1=TRUE,TRUE,FALSE).

Next, select the format option and check the box next to the
strikethrough.
Press OK twice.

The text contained in the cell will have a cut line on it

whenever you check the box.

Apply Strikethrough in Excel Online (Bonus Tip)

If you use the Excel web app, you should be pleased to know
that there is a strikethrough option on the Ribbon.

Removing Strikethrough in a Cell

Strikethrough can be easily removed from a cell. Simply press

the shortcut key (Control + 5) once more. Important
Information: If you used conditional formatting to apply it,
you could not delete it using the shortcut key without
removing all of the formattings from the cell.
How To Change Negative Numbers
In Excel To Positive

1. To convert a positive number, multiply by -1.

If you are strong at arithmetic, unlike me, you will realize that
multiplying two minus signs produces a positive result every
time. Therefore, you may change a negative integer into a
positive using the same procedure in Excel.

Simply multiplying a negative number by one will cause it to

output a positive number rather than a negative one.

=negative value*-1

Note: You can substitute the procedure below if you have

mixed numbers (both positive and negative).

=IF(A1<0,A1*-1,A1)

2. Use the ABS Function to convert to an absolute number.

With ABS, changing a negative number to a positive is simple.

This function is only used for this particular purpose.

Simply put, it can change any number into an absolute

number. In plain English, it will remove its symbol and then
return a number.
Syntax: =ABS (number)

Simply enter a negative number into the function to convert it

to a positive value.

Note that this function still functions with mixed numbers

(both positive and negative).

3. Using Paste Special Multiple

Consider an alternative scenario where you require positive

numbers to appear in the same column instead of a different
one.

You can achieve this by using the paste special option. Who
knows how? Let me explain. There are “operation” options in
the paste special option that you can utilize to carry out some
basic computations.

Negative numbers can be converted to positive numbers using

the same methods without needing a formula or an additional
column. Just carry out these actions.

First, type -1 in any cell on your spreadsheet.

Then, duplicate it.

Select the set of cells that have negative numbers now.

Right-click and choose “Paste Special,” “Operations,” and

“Multiply.”
Finally, click OK.

All of the negative numbers have now been changed to

positive numbers.

You only need to remember that this is not a dynamic

technique because of that. If you often update your data, you
must repeat the process. However, this approach is quick and
straightforward to implement, and no formula is required.

4. Flash Fill the Negative Sign.

Flash fill is a game-changer, so I’m sure you’ve used it at least

once in your life. If not, you absolutely must. Here are the
steps to a crazy trick that turns negative numbers into positive
ones.

Then, for the negative number you have in cell A2, enter the
positive number in cell B2.

Next, navigate to cell B3 and use the quick-access key Ctrl +

E.

At this time, all of the numbers in the B column are positive.

Now select “Accept Suggestions” by clicking on the tiny

button you have on column B’s right side.

Congratulations, you’ve successfully used flash fill to turn

every negative number into a positive.
Note that while quick and simple to use, this method is not
dynamic.

5. Use special formatting to make positive numbers appear.

It’s also possible that you only want to display a negative

number as a positive rather than converting it. And in this
case, custom formatting is an option. The steps are as follows.

Choose the range of cells you need to convert into positive

values first.

Then, use the quick-access key Ctrl + 1. The options for

custom formatting will be displayed.

Now select “Custom” and type “#,###;#,###” into the input

field.
Finally, click OK.

All of the negative numbers will appear positive as a result.

However, the layout only changes; these are all still negative
numbers in reality.

It is possible to verify that a cell is still a negative number by

selecting it and looking at the formula bar. Therefore, it will
act as a negative number when you utilize it in further
calculations.

6. Execute a VBA Code to Change to Positive Numbers

If you enjoy using VBA, you can quickly reverse the sign of
negative numbers by using a short piece of code.

Sub numberP2N()

Dim myCell As Range

For Each myCell In Selection

If myCell.Value <> ”” Then

If IsNumeric(myCell.Value) Then
myCell.Value = Abs(myCell.Value)

End If

End If

Next myCell

End Sub

Simply choose the range of negative values and run the macro
to use this code. It will first determine whether a numeric
value is present in each cell of the selection before converting
it to a positive value.

You cannot undo running this code once you have done so.

7. Convert Positive Numbers Using Power Query

Yes, a power query may be used to change a negative number

into a positive number, and the best thing is that it only needs
to be put up once. Simply carry out these easy steps.

Select any cells in the data range containing negative integers

to start.
After that, select “From Table” under the Data tab.

The power query editor will load the range as a table after
conversion.
Now select Transform Absolute Value from the menu when
you right-click on the column.

Finally, select the Home Tab, Close, Close, and Load options
in the power query editor.
 Organizing Worksheets In
Excel
Grouping worksheets in Excel is typically employed when you
have a collection of identically constructed sheets and would
like to modify them all at once. This is because it gives you
the best chance to edit many sheets simultaneously.

When you group worksheets, you may quickly edit, delete,

add, or do anything else on one sheet that also affects the other
grouped sheets simultaneously.
Assume for the sake of illustration that your business produces
automobile interior accessories and that the variable cost
increases somewhat each month. In this situation, if your data
set format is constant for each month, and after a few months,
you want to see the overall monthly cost, all you have to do is
group those sheets. Then, once you enter the formula in one of
the sheets to find the overall cost, the formula automatically
copies in all the grouped sheets, saving you time from having
to enter the formula in each sheet one at a time.

Instead of concealing each worksheet individually, you can

choose to hide several worksheets at once by using the
grouping worksheets option.
 How To Group Excel Worksheets

Hold the control key on your keyboard first.

Next, select each sheet tab you want to group by clicking on it

individually.

You must now let go of the control key.

All of the chosen sheets are now grouped.

In this case, click the first sheet tab while holding down the
Shift key, then click the last sheet tab if the worksheets you
want to group are contiguous.
 All Worksheets Combined

To begin, use a right-click on any workbook sheet tab.

You will then see a pop-up offering a variety of choices.

Select “Select All Sheets” from the menu now.

Your workbook sheets are now organized into groups.

 Separate Worksheets

To begin, right-click any set of sheet tabs.

You will then see a pop-up offering a variety of choices.

Select “Ungroup Sheets” from the menu now.

Your workbook is finally ungrouped.

The grouped tabs can easily be ungrouped by clicking any
sheet tab outside the group.

How to Check whether Worksheets in Excel Are Grouped

If the sheets in that workbook are grouped, the word “Group”

will always appear in the file name.

Unless someone has altered the tab color on any worksheet

tab, you will see the white background color of all the grouped
tabs.
 How To Make Blank Cells In
Excel Stand Out
A blank cell is simply a white cell with no value, so it might
be challenging to identify them in huge data sets. Most of the
time, blank cells indicate a gap in the data that is missing, or
the formula may return an empty string, leaving a blank cell.

For us to recognize these cells, it is advised to highlight

them with a color.

Which approach is the most effective for identifying and

highlighting empty cells, you might be wondering? But here’s
the shocking part: There are various approaches to this, and
you are free to choose any of them to complete your
assignment. Each of these techniques is distinct.

Therefore, I’d like to explain three distinct ways for you to

highlight the blank cell in Excel in this part. Therefore, let’s
begin.

The table below contains some empty cells.

With GoTo Special, highlight empty cells

Going to a custom option can be a simple approach to

applying color to every blank cell in a range. This approach is
easy: Apply cell color to all the selected empty cells. That’s it,
indeed. The procedures you must take to highlight every blank
cell are listed below.

First, choose all of your data.

Find and select by selecting the Home Tab. “Go To Special”

must be selected.
Choose “Blank” from the Go To Special window, then click
OK. (It will choose all of the table’s empty cells.)

Then, select the Home tab and apply the color to the cells to
make them stand out.

Apply the conditional method, the next method, if you wish to

employ a dynamic method.
Highlighting Blank Cells using Conditional Formatting

Decide on the data table first.

Navigate to Conditional Formatting under the Home tab.

Select “highlight cell rules” from the conditional formatting

options menu, then click “additional rules.”
Choose “Blank” from the drop-down option next to the rule
description.

After that, select “format” to reveal the formatting menu.

Click on the color you want to use for highlighting from the
formatting options.

Select OK.

The most admirable feature is that the processes are the same
whether you are using a pivot table or a regular table and that
it will indicate blank cells with formulas if you have any.

You need to set it up just once; you don’t need to keep doing
it. Use a table to organize your data so that conditional
formatting will grow when you add more information to the
table.
Code for Highlighting Blank Cell in VBA

I am aware of your unwavering devotion to VBA. For this

reason, we have a macro code that will color all empty cells in
a particular range.

Sub ColorBlankCells()

Dim rng As Range

Set rng = Selection

rng.SpecialCells(xlCellTypeBlanks).

Interior.Color = vbRed

End Sub

When you run this code, it will examine every cell in the range
and highlight it in red if any of them are empty.
 Excel Timestamp Insertion
(Formula + VBA + Shortcut)
You’ll discover 5 various methods for creating timestamps in
Excel in this section, and we’ll try to determine which one is
the most effective. Therefore, let’s begin.

1. Adding a timestamp using a keyboard shortcut

To insert a date and time, use one of two shortcuts. And in this
case, we must use them both in turn. the following steps:

Pick the cell where you want to put the timestamp first.

Use the shortcut key Control + after that: (Press and hold
control and then press colon). When you press it, your
system’s current date will be entered into the cell.

Your cell is currently in edit mode.

Press Control + Shift + now to: (Press and hold control, shift
key, and press colon).

Press the enter key to finish the entry while your cell is still in
edit mode.

To insert this, you must press two shortcuts consecutively.

Additionally, you can omit the shortcut key if you simply want
to change the date and time of one thing.
2. Use the NOW Function to Insert a Timestamp.

A fundamental dynamic approach. The NOW function is ideal

if you wish to insert a timestamp using a formula. According
to the settings on your system, the current date and time are
returned when you input this function into a cell.

By default, the date and time returned by NOW are in the

format mm/dd/yyyy hh:mm. However, for some reason, you
can alter its format using the custom format option if you want
a custom format. Choose the cell. Press the shortcut key
control + 1; choose “Custom”; type “mm/dd/yyyy hh:mm”
into the input box; and then click OK.

Additionally, you can use TODAY instead of NOW to enter

simply the current date because that is what the system will
return.
Pros
It uses a dynamic approach.
To establish a condition to input a timestamp if
another cell contains a value, you can combine both
functions with an IF function.

Cons
Even though it’s a dynamic method, both functions
will be updated whenever you change your
worksheet because they are volatile.
You must manually translate them into values if
you only want values, not formulas.

3. Making Timestamps Using Circular Reference

You can insert a timestamp using a circular reference if you

want to utilize an advanced technique but don’t want to use
techniques #1 and #2.

However, before you learn this technique, let’s first understand

what circular reference is all about. Say cell A1 contains the
value 5, and cell B1 contains the value 10. A circular reference
error warning will appear if you enter the formula
=A1+B1+C1 in cell C1.
This results from cell C1 being used as a reference in cell C1.
When a circular reference mistake occurs, the cell develops a
non-ending loop. For example, the value of cell A3 depends
on the reference to cell A3, and the value of cell A3 depends
on the reference to cell A3.

However, Excel does not generate a circular reference when

one is entered; therefore, the non-ending loop never begins.

Let’s get to it:

By turning on the “iterative calculation option,” you can make

Excel employ the now function and complete the computation
at least once. In this manner, Excel will change the cell
formula once rather than repeatedly. Iterative calculation
option activation steps:

Select File > Options.

Select Formulas from the list of Excel choices.

Check the Enable iterative calculation box under the

Calculated settings.

Select OK.

Next, write the following formula in cell B2’s formula bar:

=IF(A2>””,IF(B2>””,B2,NOW()),””)

Now, the formula in cell B2 will output a timestamp whenever

you enter any value in cell A2.
4. Adding a Timestamp Using VBA

I’m sure you’ll find this VBA code beneficial if you’re a VBA
nut. You don’t need to use a shortcut key or enter a formula.
Simply choose the cell that needs to have a timestamp entered
and execute the macro.

Sub timeStamp()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = “m/d/yyyy h:mm:ss AM/PM”
End With
End Sub

Application of this code

You can run this code each time you need to add a timestamp
by adding it to your QAT (quick access toolbar).

the following steps:

Open the VBA editor and add this code to the module.
Go to File -> Options -> Quick Access Toolbar after that.

From the drop-down menu labeled “Choose Command from,”

select “Macros.”

Add timeStamp macro to QAT after that.

Select that macro now, then click Modify.

Click OK after selecting an icon.

Now that you have an icon on QAT, you can pick the cell and
click this button to insert a timestamp whenever you need one.

Utilizing UDF as a Timestamp

Yes, you can develop your own unique Excel function to add a
timestamp in Excel. The code for this UDF is shown below.

Function Timestamp(Reference As Range)

If Reference.
Value <> ”” Then
Timestamp = Format(Now, “dd-mm-yyyy hh:mm:ss”)
Else
Timestamp = ””
End If
End Function
If another cell contains a value, you can use this user-defined
function to obtain a timestamp in that cell. Please take the
following actions:

Open the VBA editor by selecting the Developer tab.

In the VBA editor, insert a new module and paste this code
into it.

Close the VBA editor at this point, then return to your

worksheet.

Enter the following formula in cell B2.

Cell B1 will receive a timestamp when you enter any value in

cell A1.
 Excel Bullet Point Insertion
Techniques
Dialog Box to Add a Bullet Point from a Symbol

You have probably used this technique previously.

Select the cell you want to include the bullet point to.

Go to the Insert tab and select symbol.

A symbol dialog box will appear after you click the symbol
button.

Many intelligent symbols are available in the Symbol dialog

box that can be used as bullet points, or you can just use a
black dot.

Simply click the insert button after selecting your bullet point
to add it to the cell.
Here are various symbols you can use as bullet points in an
Excel list.

Using a Shortcut Key, add bullet points to the text.

Shortcut keys are the ideal approach to entering a bullet point

if you don’t have the time to fiddle with a symbol dialog box.
You can use one of the two shortcut keys that we have
available.
Decide the cell you wish to add a bullet point to.

To edit the cell, press F2.

To insert a bullet, press 7 or 9 while holding the alt key.

Different sorts of bullets will appear when you press either

shortcut key. Using the keyboard shortcuts Alt + 7 or Alt + 9,
you can add a dark dot or a circle for bullet points.
 Conclusion
By now, I’m hoping you’ve already started and taken the most
crucial step—action. Reading and gathering information is
excellent, but it’s useless until you put it to use. And action
will be the one step many individuals with good intentions will
take.

Also, if you’ve not gotten book one of this series, do well to

get it and get book three as well because I’ll be revealing
many more amazing tips and tricks on becoming an expert in
Microsoft Excel.

I wish you luck—be wise and kick some ass.

Kindly leave some good reviews if this book gave you some
value.

Cheers!