Set Background in M S Excel

Unfortunately, you cannot have a background image on your printouts. You may have noticed the Page Layout » Page Setup » Background command. This button displays a dialogue box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed.


(toc)


Alternative to Placing Background

  • You can insert a Shape, WordArt, or a picture on your worksheet and then adjust its transparency. Then copy the image to all printed pages.

  • You can insert an object in a page header or footer.

Setting Background only Display in Sheet
Setting Background only Display in Sheet

Freeze Panes in M S Excel

If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. MS Excel provides a handy solution to this problem with freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.


(toc)


Using Freeze Panes

Follow the steps mentioned below to freeze panes.

  • Select the First row or First Column or the row Below, which you want to freeze, or Column right to area, which you want to freeze.

  • Choose View Tab » Freeze Panes.

  • Select the suitable option −

    • Freeze Panes − To freeze area of cells.

    • Freeze Top Row − To freeze first row of worksheet.

    • Freeze First Column − To freeze first Column of worksheet.

Freeze Panes Use
Freeze Panes Use

  • If you have selected Freeze top row you can see the first row appears at the top, after scrolling also. See the below screen-shot.

First Row Freezed
First Row Freezed

Unfreeze Panes

To unfreeze Panes, choose View Tab » Unfreeze Panes.

Conditional Format in M S Excel

MS Excel 2010 Conditional Formatting feature enables you to format a range of values so that the values outside certain limits, are automatically formatted.


(toc)


Conditional Formatting

Choose Home Tab » Style group » Conditional Formatting dropdown.

Various Conditional Formatting Options

  • Highlight Cells Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text, or dates, or that have values greater or less than a particular value, or that fall within a certain ranges of values.

Suppose you want to find cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To.

Highlighting Cells
Highlighting Cells

After Clicking ok, the cells with value zero are marked as red.

Applied Conditional Formatting
Applied Conditional Formatting

  • Top/Bottom Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the top and bottom values, percentages, and above and below average values in the cell selection.

Suppose you want to highlight the top 10% rows you can do this with these Top/Bottom rules.

Select top 10%
Select top 10%

  • Data Bars − It opens a palette with different color data bars that you can apply to the cell selection to indicate their values relative to each other by clicking the data bar thumbnail.

With this conditional Formatting data Bars will appear in each cell.

Data Bar Filter condition
Data Bar Filter condition

  • Color Scales − It opens a palette with different three- and two-colored scales that you can apply to the cell selection to indicate their values relative to each other by clicking the color scale thumbnail.

See the below screenshot with Color Scales, conditional formatting applied.

Applying Color Scales Conditional Formatting
Applying Color Scales Conditional Formatting

  • Icon Sets − It opens a palette with different sets of icons that you can apply to the cell selection to indicate their values relative to each other by clicking the icon set.

See the below screenshot with Icon Sets conditional formatting applied.

Icon Set Conditional Formatting
Icon Set Conditional Formatting

  • New Rule − It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection.

  • Clear Rules − It opens a continuation menu, where you can remove the conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the Entire Sheet option, or for just the current data table by clicking the This Table option.

  • Manage Rules − It opens the Conditional Formatting Rules Manager dialog box, where you edit and delete particular rules as well as adjust their rule precedence by moving them up or down in the Rules list box.

Domain Specific Tools

Depending on its usage, software may be generic or specific. Generic software is a software that can perform multiple tasks in different scenarios without being modified. For example, a word processor software can be used by anyone to create different types of documents like report, whitepaper, training material, etc. Specific software is a software for a particular application, like railway reservation system, weather forecasting, etc. Let us look at some examples of domain specific tools.


(toc)


School Management System

School management system handles the diverse activities of a school like examination, attendance, admission, students’ fees, time table, teachers’ training, etc.

School Management

Inventory Management

Managing multiple activities like purchase, sales, order, delivery, stock maintenance, etc. associated with raw or processed goods in any business is called inventory management. The inventory management software ensures that stocks are never below specified limits and purchase/deliveries are done in time.

Payroll Software

Payroll software handles complete salary calculations of employees, taking care of leave, bonus, loans, etc. Payroll software is usually a component of HR (human resource) management software in mid-sized to big organizations.

Payroll

Financial Accounting

Financial management software keeps an electronic record of all financial transactions of the organization. It has many functional heads like account receivables, accounts payable, loans, payroll, etc.

Financial Accounting

Restaurant Management

Restaurant management software helps restaurant managers in keeping track of inventory levels, daily orders, customer management, employee scheduling, table bookings, etc.

Restaurant Management

Railway Reservation System

Railway reservation system is a software that handles multiple modules like train routes, train management, seat booking, food booking, train maintenance, train status, travel packages, etc.

Railway Reservation System

Weather Forecasting System

Weather forecasting system is a real-time software that predicts weather of a place by collecting copious amount of live data about atmospheric temperature, humidity, wind level, etc. It is used to predict major disasters like earthquakes, hurricanes, tsunamis, etc.

weather forecasting system

Computer – Overview

Today’s world is an information-rich world and it has become a necessity for everyone to know about computers. A computer is an electronic data processing device, which accepts and stores data input, processes the data input, and generates the output in a required format.


(toc)


The purpose of this tutorial is to introduce you to Computers and its fundamentals.

Functionalities of a Computer

If we look at it in a very broad sense, any digital computer carries out the following five functions −

Step 1 − Takes data as input.

Step 2 − Stores the data/instructions in its memory and uses them as required.

Step 3 − Processes the data and converts it into useful information.

Step 4 − Generates the output.

Step 5 − Controls all the above four steps.

Computer

Advantages of Computers

Following are certain advantages of computers.

High Speed

  • Computer is a very fast device.

  • It is capable of performing calculation of very large amount of data.

  • The computer has units of speed in microsecond, nanosecond, and even the picosecond.

  • It can perform millions of calculations in a few seconds as compared to man who will spend many months to perform the same task.

Accuracy

  • In addition to being very fast, computers are very accurate.

  • The calculations are 100% error free.

  • Computers perform all jobs with 100% accuracy provided that the input is correct.

Storage Capability

  • Memory is a very important characteristic of computers.

  • A computer has much more storage capacity than human beings.

  • It can store large amount of data.

  • It can store any type of data such as images, videos, text, audio, etc.

Diligence

  • Unlike human beings, a computer is free from monotony, tiredness, and lack of concentration.

  • It can work continuously without any error and boredom.

  • It can perform repeated tasks with the same speed and accuracy.

Versatility

  • A computer is a very versatile machine.

  • A computer is very flexible in performing the jobs to be done.

  • This machine can be used to solve the problems related to various fields.

  • At one instance, it may be solving a complex scientific problem and the very next moment it may be playing a card game.

Reliability

  • A computer is a reliable machine.

  • Modern electronic components have long lives.

  • Computers are designed to make maintenance easy.

Automation

  • Computer is an automatic machine.

  • Automation is the ability to perform a given task automatically. Once the computer receives a program i.e., the program is stored in the computer memory, then the program and instruction can control the program execution without human interaction.

Reduction in Paper Work and Cost

  • The use of computers for data processing in an organization leads to reduction in paper work and results in speeding up the process.

  • As data in electronic files can be retrieved as and when required, the problem of maintenance of large number of paper files gets reduced.

  • Though the initial investment for installing a computer is high, it substantially reduces the cost of each of its transaction.

Disadvantages of Computers

Following are certain disadvantages of computers.

No I.Q.

  • A computer is a machine that has no intelligence to perform any task.

  • Each instruction has to be given to the computer.

  • A computer cannot take any decision on its own.

Dependency

  • It functions as per the user’s instruction, thus it is fully dependent on humans.

Environment

  • The operating environment of the computer should be dust free and suitable.

No Feeling

  • Computers have no feelings or emotions.

  • It cannot make judgment based on feeling, taste, experience, and knowledge unlike humans.

Commonly used computer keyboard short-cut keys

Commonly used computer keyboard short-cut keys
Commonly used computer keyboard short-cut keys

(toc)

Function Short-cut keys
Commonly used computer keyboard short-cut keys
F1 Universal help (for any sort of program).
Alt + F File menu options in current program.
Alt + E Edits options in current program.
Ctrl + A Selects all text.
Ctrl + X Cuts the selected item.
Ctrl + C Copies the selected item.
Ctrl + V Pastes copied item.
Home Takes the user to the beginning of the current line.
End Takes the user to the end of the current line.
Ctrl + Home Takes the user to the beginning of the document.
Ctrl + End Takes the user to the end of the document.
Shift + Home Highlights from the current place to the beginning of line.
Shift + End Highlights from the current place to the end of line.

Microsoft Windows Shortcut Keys
Ctrl + F4 Closes window in program.
Alt + F4 Closes current open program.
F2 Renames the selected icon.
F3 Start find from desktop.
F4 Opens the drive selection when browsing.
F5 Refreshes contents.
Alt + Tab Switches from one open application to another open application.
Alt + Shift + Tab Switches backwards between open applications.
Alt + Print Screen Creates screen shot for current program.
Ctrl + Alt + Del. Opens windows task manager/reboot.
Ctrl + Esc Brings up start menu.
Alt + Esc Switches between applications on taskbar.
Ctrl + Plus (+) Key Automatically adjusts widths of all columns in Windows Explorer.
Alt + Enter Opens properties window of selected icon or program.
Shift + F10 Simulates right-click on selected item.
Shift + Del Deletes selected programs/files permanently.
Holding Shift During Boot-up Enables boot safe mode or bypass system files.
Ctrl + N Starts a new note.
Ctrl + O Opens a recently used note.
Ctrl + S Saves changes to a note.
Ctrl + P Prints a note.
Alt + F4 Closes a note and its Journal window.
Ctrl + Z Helps Undo a change.
Ctrl + Y Helps Redo a change.
Ctrl + A Selects all items on a page.
Ctrl + X Cuts a selection.
Ctrl + C Copies a selection to the Clipboard.
Ctrl + V Pastes a selection from the Clipboard.
Esc Cancels a selection.
Ctrl + F Start a search tool.
Ctrl + Shift + C Display a shortcut menu for column headings in a note list.

Microsoft Word Shortcut Keys
Ctrl + N Creates a new document.
Ctrl + O Opens an existing document.
Ctrl + S Saves changes to a document.
F12 Saves the document as a new file.
Ctrl + P Prints a document.
Ctrl + Z Helps Undo a change.
Ctrl + Y Helps Redo a change.
Ctrl + A Selects the whole document.
Ctrl + X Helps cut a selection
Ctrl + C Copies a selection to the Clipboard.
Ctrl + V Pastes a selection from the Clipboard.
Ctrl + B Makes selected text bold.
Ctrl + I Italicizes selected text.
Ctrl + U Underlines selected texts.
Ctrl + L Aligns text left.
Ctrl + R Aligns text right.
Ctrl + E Aligns text center.
Ctrl + J Helps justify text.
Ctrl + 1 Sets single line spacing.
Ctrl + 2 Sets double line spacing.
Ctrl + 5 Sets line spacing to 1.5.
Ctrl + Shift + A Changes characters to all capitals.
Ctrl + D Inserts a Microsoft Paint drawing.
Ctrl + F Finds text
Ctrl + Home Moves to the beginning of the document.
Ctrl + End Moves to the end of the document.

All modern computers and computing devices

All modern computers and computing devices
All modern computers and computing devices

Historically computers were classified according to processor types because development in processor and processing speeds were the developmental benchmarks. Earliest computers used vacuum tubes for processing, were huge and broke down frequently. However, as vacuum tubes were replaced by transistors and then chips, their size decreased and processing speeds increased manifold.

All modern computers and computing devices use microprocessors whose speeds and storage capacities are skyrocketing day by day. The developmental benchmark for computers is now their size. Computers are now classified on the basis of their use or size −

  • Desktop
  • Laptop
  • Tablet
  • Server
  • Mainframe
  • Supercomputer

Let us look at all these types of computers in detail.

(toc)

Desktop

Desktop computers are personal computers (PCs) designed for use by an individual at a fixed location. IBM was the first computer to introduce and popularize use of desktops. A desktop unit typically has a CPU (Central Processing Unit), monitor, keyboard and mouse. Introduction of desktops popularized use of computers among common people as it was compact and affordable.

Desktop
Desktop

Riding on the wave of desktop’s popularity many software and hardware devices were developed specially for the home or office user. The foremost design consideration here was user friendliness.

Laptop

Despite its huge popularity, desktops gave way to a more compact and portable personal computer called laptop in 2000s. Laptops are also called notebook computers or simply notebooks. Laptops run using batteries and connect to networks using Wi-Fi (Wireless Fidelity) chips. They also have chips for energy efficiency so that they can conserve power whenever possible and have a longer life.

Laptop
Laptop

Modern laptops have enough processing power and storage capacity to be used for all office work, website designing, software development and even audio/video editing.

Tablet

After laptops computers were further miniaturized to develop machines that have processing power of a desktop but are small enough to be held in one’s palm. Tablets have touch sensitive screen of typically 5 to 10 inches where one finger is used to touch icons and invoke applications.

Tablet
Tablet

Keyboard is also displayed virtually whenever required and used with touch strokes. Applications that run on tablets are called apps. They use operating systems by Microsoft (Windows 8 and later versions) or Google (Android). Apple computers have developed their own tablet called iPad which uses a proprietary OS called iOS.

Server

Servers are computers with high processing speeds that provide one or more services to other systems on the network. They may or may not have screens attached to them. A group of computers or digital devices connected together to share resources is called a network.

Server
Server

Servers have high processing powers and can handle multiple requests simultaneously. Most commonly found servers on networks include −

  • File or storage server
  • Game server
  • Application server
  • Database server
  • Mail server
  • Print server

Mainframe

Mainframes are computers used by organizations like banks, airlines and railways to handle millions and trillions of online transactions per second. Important features of mainframes are −

  • Big in size
  • Hundreds times Faster than servers, typically hundred megabytes per second
  • Very expensive
  • Use proprietary OS provided by the manufacturers
  • In-built hardware, software and firmware security features

Supercomputer

Supercomputers are the fastest computers on Earth. They are used for carrying out complex, fast and time intensive calculations for scientific and engineering applications. Supercomputer speed or performance is measured in teraflops, i.e. 1012 floating point operations per second.

Supercomputers
Supercomputers

Chinese supercomputer Sunway TaihuLight is the world’s fastest supercomputer with a rating of 93 petaflops per second, i.e. 93 quadrillion floating point operations per second.

Most common uses of supercomputers include 

  • Molecular mapping and research
  • Weather forecasting
  • Environmental research
  • Oil and gas exploration

Microsoft Office Tools

Application software that assist users in regular office jobs like creating, updating and maintaining documents, handling large amounts of data, creating presentations, scheduling, etc. are called office tools. Using office tools saves time and effort and lots of repetitive tasks can be done easily. Some of the software that do this are 

  • Word processors
  • Spreadsheets
  • Database systems
  • Presentation software
  • E-mail tools

Let us look at some of these in detail.

(toc)

Word Processor

A software for creating, storing and manipulating text documents is called word processor. Some common word processors are MS-Word, WordPad, WordPerfect, Google docs, etc.

Word Processor
Word Processor

A word processor allows you to −

  • Create, save and edit documents
  • Format text properties like font, alignment, font color, background color, etc.
  • Check spelling and grammar
  • Add images
  • Add header and footer, set page margins and insert watermarks

Spreadsheet

Spreadsheet is a software that assists users in processing and analyzing tabular data. It is a computerized accounting tool. Data is always entered in a cell (intersection of a row and a column) and formulas and functions to process a group of cells is easily available. Some of the popular spreadsheet software include MS-Excel, Gnumeric, Google Sheets, etc. Here is a list of activities that can be done within a spreadsheet software −

  • Simple calculations like addition, average, counting, etc.
  • Preparing charts and graphs on a group of related data
  • Data entry
  • Data formatting
  • Cell formatting
  • Calculations based on logical comparisons
Spreadsheet
Spreadsheet

Presentation Tool

Presentation tool enables user to demonstrate information broken down into small chunks and arranged on pages called slides. A series of slides that present a coherent idea to an audience is called a presentation. The slides can have text, images, tables, audio, video or other multimedia information arranged on them. MS-PowerPoint, OpenOffice Impress, Lotus Freelance, etc. are some popular presentation tools.

Presentation Tool
Presentation Tool

Database Management System

Software that manages storage, updating and retrieval of data by creating databases is called database management system. Some popular database management tools are MS-Access, MySQL, Oracle, FoxPro, etc.

Database Management System
Database Management System

Microprocessor Concepts

Microprocessor is the brain of computer, which does all the work. It is a computer processor that incorporates all the functions of CPU (Central Processing Unit) on a single IC (Integrated Circuit) or at the most a few ICs. Microprocessors were first introduced in early 1970s. 4004 was the first general purpose microprocessor used by Intel in building personal computers. Arrival of low cost general purpose microprocessors has been instrumental in development of modern society the way it has.

(toc)

Microprocessor
Microprocessor

We will study the characteristics and components of a microprocessor in detail.

Microprocessors Characteristics

Microprocessors are multipurpose devices that can be designed for generic or specialized functions. The microprocessors of laptops and smartphones are general purpose whereas ones designed for graphical processing or machine vision are specialized ones. There are some characteristics that are common to all microprocessors.

These are the most important defining characteristics of a microprocessor −

  • Clock speed
  • Instruction set
  • Word size

Clock Speed

Every microprocessor has an internal clock that regulates the speed at which it executes instructions and also synchronizes it with other components. The speed at which the microprocessor executes instructions is called clock speed. Clock speeds are measured in MHz or GHz where 1 MHz means 1 million cycles per second whereas 1 GHz equals to 1 billion cycles per second. Here cycle refers to single electric signal cycle.

Currently microprocessors have clock speed in the range of 3 GHz, which is maximum that current technology can attain. Speeds more than this generate enough heat to damage the chip itself. To overcome this, manufacturers are using multiple processors working in parallel on a chip.

Word Size

Number of bits that can be processed by a processor in a single instruction is called its word size. Word size determines the amount of RAM that can be accessed at one go and total number of pins on the microprocessor. Total number of input and output pins in turn determines the architecture of the microprocessor.

First commercial microprocessor Intel 4004 was a 4-bit processor. It had 4 input pins and 4 output pins. Number of output pins is always equal to the number of input pins. Currently most microprocessors use 32-bit or 64-bit architecture.

Instruction Set

A command given to a digital machine to perform an operation on a piece of data is called an instruction. Basic set of machine level instructions that a microprocessor is designed to execute is called its instruction set. These instructions do carry out these types of operations −

  • Data transfer
  • Arithmetic operations
  • Logical operations
  • Control flow
  • Input/output and machine control

Microprocessor Components

Compared to the first microprocessors, today’s processors are very small but still they have these basic parts right from the first model −

  • CPU
  • Bus
  • Memory

CPU

CPU is fabricated as a very large scale integrated circuit (VLSI) and has these parts −

  • Instruction register − It holds the instruction to be executed.

  • Decoder − It decodes (converts to machine level language) the instruction and sends to the ALU (Arithmetic Logic Unit).

  • ALU − It has necessary circuits to perform arithmetic, logical, memory, register and program sequencing operations.

  • Register − It holds intermediate results obtained during program processing. Registers are used for holding such results rather than RAM because accessing registers is almost 10 times faster than accessing RAM.

Bus

Connection lines used to connect the internal parts of the microprocessor chip is called bus. There are three types of buses in a microprocessor −

  • Data Bus − Lines that carry data to and from memory are called data bus. It is a bidirectional bus with width equal to word length of the microprocessor.

  • Address Bus − It is a unidirectional responsible for carrying address of a memory location or I/O port from CPU to memory or I/O port.

  • Control Bus − Lines that carry control signals like clock signals, interrupt signal or ready signal are called control bus. They are bidirectional. Signal that denotes that a device is ready for processing is called ready signal. Signal that indicates to a device to interrupt its process is called an interrupt signal.

Memory

Microprocessor has two types of memory

  • RAM − Random Access Memory is volatile memory that gets erased when power is switched off. All data and instructions are stored in RAM.

  • ROM − Read Only Memory is non-volatile memory whose data remains intact even after power is switched off. Microprocessor can read from it any time it wants but cannot write to it. It is preprogrammed with most essential data like booting sequence by the manufacturer.

Primary Memory

Memory is required in computers to store data and instructions. Memory is physically organized as a large number of cells that are capable of storing one bit each. Logically they are organized as groups of bits called words that are assigned an address. Data and instructions are accessed through these memory address. The speed with which these memory addresses can be accessed determines the cost of the memory. Faster the memory speed, higher the price.

(toc)

Computer memory can be said to be organized in a hierarchical way where memory with the fastest access speeds and highest costs lies at the top whereas those with lowest speeds and hence lowest costs lie at the bottom. Based on this criteria memory is of two types – primary and secondary. Here we will look at primary memory in detail.

The main features of primary memory, which distinguish it from secondary memory are −

  • It is accessed directly by the processor
  • It is the fastest memory available
  • Each word is stored as well as
  • It is volatile, i.e. its contents are lost once power is switched off

As primary memory is expensive, technologies are developed to optimize its use. These are broad types of primary memory available.

Primary Memory
Primary Memory

RAM

RAM stands for Random Access Memory. The processor accesses all memory addresses directly, irrespective of word length, making storage and retrieval fast. RAM is the fastest memory available and hence most expensive. These two factors imply that RAM is available in very small quantities of up to 1GB. RAM is volatile but my be of any of these two types

DRAM (Dynamic RAM)

Each memory cell in a DRAM is made of one transistor and one capacitor, which store one bit of data. However, this cell starts losing its charge and hence data stored in less than thousandth of a second. So it needs to be refreshed thousand times a second, which takes up processor time. However, due to small size of each cell, one DRAM can have large number of cells. Primary memory of most of the personal computers is made of DRAM.

SRAM (SRAM)

Each cell in SRAM is made of a flip flop that stores one bit. It retains its bit till the power supply is on and doesn’t need to be refreshed like DRAM. It also has shorter read-write cycles as compared to DRAM. SRAM is used in specialized applications.

ROM

ROM stands for Read Only Memory. As the name suggests, ROM can only be read by the processor. New data cannot be written into ROM. Data to be stored into ROM is written during the manufacturing phase itself. They contain data that does not need to be altered, like booting sequence of a computer or algorithmic tables for mathematical applications. ROM is slower and hence cheaper than RAM. It retains its data even when power is switched off, i.e. it is non-volatile. ROM cannot be altered the way RAM can be but technologies are available to program these types of ROMs −

PROM (Programmable ROM)

PROM can be programmed using a special hardware device called PROM programmer or PROM burner.

EPROM (Erasable Programmable ROM)

EPROM can be erased and then programmed using special electrical signals or UV rays. EPROMs that can be erased using UV rays are called UVEPROM and those that can be erased using electrical signals are called EEPROM. However, handling electric signals is easier and safer than UV rays.

Cache Memory

Small piece of high speed volatile memory available to the processor for fast processing is called cache memory. Cache may be a reserved portion of main memory, another chip on CPU or an independent high speed storage device. Cache memory is made of fast speed SRAMs. The process of keeping some data and instructions in cache memory for faster access is called caching. Caching is done when a set of data or instructions is accesses again and again.

Whenever the processor needs any piece of data or instructions, it checks the cache first. If it is unavailable there, then the main memory and finally secondary memory is accessed. As cache has very high speed, time spent in accessing it every time is negligible as compared to time saved if data indeed is in the cache. Finding data or instruction in cache is called cache hit.