Microsoft office excel 2010 formula tutorial pdf free
Looking for:
Microsoft office excel 2010 formula tutorial pdf free. Microsoft Excel Tutorial for Beginners: Learn MS Excel in 7 Days |
Microsoft office excel 2010 formula tutorial pdf free -
Excel formulas and functions help you perform your tasks efficiently, and it's time-saving. Let's proceed and learn the different types of functions available in Excel and use relevant formulas as and when required.
There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset. We will look into the formulas and functions on mathematical operations, character-text functions, data and time, sumif-countif, and few lookup functions. In this article, we have categorized 25 Excel formulas based on their operations. The SUM function, as the name suggests, gives the total of the selected range of cell values.
It performs the mathematical operation which is addition. This automatically adds up , , and The result is stored in C5. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric. As seen above, here, we are counting from C1 to C4, ideally four cells.
Depending on what you want, you can select either average, count, sum, min, max, min, and others. In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5.
The MOD function works on returning the remainder when a particular number is divided by a divisor. This is how power function works in Excel. Next, we have the ceiling function.
Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance. This function merges or joins several text strings into one text string.
Given below are the different ways to perform this function. The function LEN returns the total number of characters in a string.
So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function. The LEFT function gives the number of characters from the start of a text string. Meanwhile, the MID function returns the characters from the middle of a text string, given a starting position and length.
Finally, the right function returns the number of characters from the end of a text string. Start Learning. The function DAY is used to return the day of the month. It will be a number between 1 to The TIME function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.
The HOUR function generates the hour from a time value as a number from 0 to Here, 0 means 12 AM and 23 is 11 PM. This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table.
It then returns a value in the same row from a column you specify. Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup. It gives the value in the same column from a row you specify. Here, H23 has the lookup value, i. In the below example, we want to check if the value in cell A2 is greater than 5.
This function returns a value if an expression evaluates to an error, or else it will return the value of the expression. Suppose you want to divide 10 by 0. It will result in an error.
This is because it needs to evaluate the entire table array which you've selected. Below is a coronavirus sample dataset with information regarding the coronavirus cases and deaths in each country and region. If you want to count the number of days in which the cases in India have been greater than Select with Mouse Drag the mouse over the data you want to select.
It will select those cells as shown below. Select with Special If you want to select specific region, select any cell Pressing F5 will show the below dialogue box. Select current region from the radio buttons. Click on ok to see the current region selected. Delete with Mouse Select the data you want to delete.
Right Click on the sheet. Select the delete option, to delete the data. Delete with Delete Key Select the data you want to delete. Press on the Delete Button from the keyboardto delete the data. Then right click to show the various options. Select the Delete option to delete the selected rows.
Step 1: Select the data you want to Move. Right Click and select the cut option. Right click on it and paste the data. You can see the data is moved now.
For MS Excel , total rows, total columns. Row and numbers Columns ranges ranges from 1 to ; in from A to XFD; in Navigation with Rows and Columns Let us see how to move to the last row or the last column. Cell is identified with Combination of column header and row number.
For example: A1, A2 49 The simplest method of copy paste is as below. In this case, MS Excel will copy everything such as values, formulas, Formats, Comments and validation. MS Excel will overwrite the content with paste. View the clipboard content. Select the cell where you need to paste. Click on paste, to paste the content. For example, you want to copy only Values or you want to copy only the formatting of cells. Select the paste special option as shown below.
When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed.
See the image below. You can see the Find and Replace dialogue as below. We can get rid of the spelling mistakes with the help of spelling check feature. Spell Check Basis Let us see how to access the spell check. You can view the zoom slider at the right bottom of the workbook as shown below. Zoom In You can zoom in the workbook by moving the slider to the right. It will change the only view of the workbook. See the below screen-shot. You can see many symbols available there like Pi, alpha, beta, etc.
Select the symbol you want to add and click insert to use the symbol. You can see many special characters available there like Copyright, Registered etc. Select the special character you want to add and click insert, to use the special character.
It helps in proper documentation. To add comment to a cell, select the cell and perform any of the actions mentioned below. Initially, a comment consists of Computer's user name. You have to modify it with text for the cell comment. For formatting a comment, Right click on cell » Edit comment » Select comment » Right click on it » Format comment. With formatting of comment you can change the color, font, size, etc. Text boxes and cell comments are similar in displaying the text in rectangular box.
But text boxes are always visible, while cell comments become visible after selecting the cell. Adding Text Boxes To add a text box, perform the below actions. Initially, the comment consists of Computer's user name. Let us see some of the important options of formatting a text box. Also specifying the transparency of text box fill. We can undo changes in following two ways. You can reverse the effects of the past actions that you performed by executing Undo more than once.
If you click the arrow on the right side of the Undo button, you see a list of the actions that you can reverse. Click an item in that list to undo that action and all the subsequent actions you performed. We can redo changes in following two ways. Various Cell Formats Below are the various cell formats.
Setting Font from Home You can set the font of the selected text from Home » Font group » select the font.
Text-Decoration Various options are available in Home tab of the ribbon as mentioned below. Choose Format cells » Alignment » Set the degree for rotation.
You can change it as per your need from Home tab » Font group » Background color. You can change it as per your need from Home tab » Font group » Foreground color. Below are the various ways of doing it. By default, Excel aligns numbers to the right and text to the left. Click on the available option in the Alignment group in Home tab to change alignment. Change Alignment from Format Cells Right click on the cell and choose format cell. In format cells dialogue, choose Alignment Tab.
Select the available options from the Vertical alignment and Horizontal alignment options. This option is applicable only if the cell is formatted as wrapped text and uses more than one line. Vertical Alignment: You can set Vertical alignment to top, Middle, bottom, etc.
Rather, you combine a group of cells into a single cell that occupies the same space. You can merge cells by various ways as mentioned below. Various tabs are available as shown below. Page Order: o Down, then Over: It prints the down pages first and then the right pages. All printed pages in MS Excel have the same margins. You can set margins by various ways as explained below.
If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab as shown in the above screenshot.
If you change the orientation, the onscreen page breaks adjust automatically to accommodate the new paper orientation. By default, new workbooks do not have headers or footers.
You can choose the predefined header and footer or create your custom ones. Accordingly if the document is scaled when printed. This option is enabled, by default. MS Excel gives you precise control over page breaks. MS Excel handles page breaks automatically, but sometimes you may want to force a page break either a vertical or a horizontal one, so that the report prints the way you want. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.
Insert vertical Page break: In this case, make sure to place the pointer in row 1. 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. Then copy the image to all printed pages. MS Excel provides a handy solution to this problem with freezing panes. Using Freeze Panes Follow the steps mentioned below to freeze panes. Suppose you want to find cell with Amount 0 and Mark them as red. After Clicking ok, the cells with value zero are marked as red. With this conditional Formatting, data Bars will appear in each cell.
See the below screenshot with Color Scales, conditional formatting applied. See the below screenshot with Icon Sets, conditional formatting applied. Without formula, worksheet will be just simple tabular representation of data. A formula consists of special code, which is entered into a cell. It performs some calculations and returns a result, which is displayed in the cell. Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature.
For example, you can quickly change the data in a worksheet and formulas works. This formula uses only values, and it always returns the same result as When building formulas manually, you can either type in the cell addresses or you can point to them in the worksheet.
Using the Pointing method to supply the cell addresses for formulas is often easier and more powerful method of formula building. See the below screen shot. As soon as you complete a formula entry, Excel calculates the result, which is then displayed inside the cell within the worksheet the contents of the formula, however, continue to be visible on the Formula bar anytime the cell is active.
If you make an error in the formula that prevents Excel from being able to calculate the formula at all, Excel displays an Alert dialog box suggesting how to fix the problem. When a formula uses cell than constant values, Excel makes the task of copying an every place that requires a similar formula. Relative Cell Addresses MS Excel does it automatically adjusting the cell references in the original formula to suit the position of the copies that you make.
It does this through a system known as relative cell addresses, where by the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position. Let us see this with the help of example. Suppose we want the sum of all the rows at last, then we will write a formula for first column i. We want sum of the rows from 3 to 8 in the 9th row. After dragging we can see the formula in the remaining columns as below.
These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects new value automatically. When you use a cell or range reference in a formula, you can use three types of references: relative, absolute, and mixed references.
Relative Cell References The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas. Absolute Cell References The row and column references do not change when you copy the formula because the reference is to an actual cell address. Mixed Cell References Both the row or column reference is relative and the other is absolute.
These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult if you use only the operators. Suppose you need to determine the largest value in a range. Suppose you want to find if the cell of month is greater than then we can give Bonus to Sales representative. The information inside the parentheses is the list of arguments.
Functions vary in how they use arguments. Depending on what it has to do, a function may use. To see all the functions by category, choose Formulas Tab » Insert Function.
Then Insert function Dialog appears from which we can choose the function. Statistical o MAX: Returns the largest value from a list of supplied numbers. The other rows gets hidden. Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this.
Follow the below mentioned steps to do this. Suppose after size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter for Shoe Size, choose Color column and then set filter for color. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort data by Amount from smallest to largest or largest to smallest. To Sort the data follow the steps mentioned below. Clicking Ok will sort the data. You can see the same dialog to sort records.
A cell is identified by its address, which consists of its column letter and row number. For example, cell B1 is the cell in the second column and the first row. A group of cells is called a range. Then release the mouse button.
If you drag to the end of the screen, the worksheet will scroll. Press F8 again to return the navigation keys to normal movement. Excel selects the cell or range that you specified. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and If the user makes an invalid entry, you can display a custom message as shown below. Validation Criteria To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box.
Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice. For example, you can specify that the entry must be a whole number greater than or equal to For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to You will create drop-down list with this validation.
You have to give input ranges then those values will appear in the drop-down. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, , and less than or equal to December 31, You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than p.
You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 a single alphanumeric character.
Fill the title and Input message of the Input message tab and the input message will appear when the cell is selected. Fill the title and error message. Select the style of the error as stop, warning or Information as per you need.
It saves time as well as makes sure that look of the cells are consistent. Suppose that you apply a particular style to some twenty cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 12 pt.
Rather than changing each cell, simply edit the style. All cells with that particular style change automatically. Note that this display is a live preview, that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection.
This will add new style in the styles. You can view it on Home » Styles. By using themes, it is easy to specify the colors, fonts, and a variety of graphic effects in a document.
And best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook. Note that this display is a live preview, that is, as you move your mouse over the Theme, it temporarily displays the theme effect. You can select a template stored on your hard drive, or a template from Microsoft Office Online. If you choose a template from Microsoft Office Online, you must be connected to the Internet to download it.
The Office Online Templates section contains a number of icons, which represents various categories of templates. When you select a template thumbnail, you can see a preview in the right panel. When you select the template and click on it, it will download the template data from Microsoft server and opens it as shown below.
By using macro recorder from View Tab » Macro Dropdown to record tasks that you perform routinely, you not only speed up the procedure considerably but you are assured that each step in a task is carried out the same way each and every time you perform a task.
To view macros choose View Tab » Macro dropdown. Macro Options View tab contains a Macros command button to which a dropdown menu containing the following three options. Macro will record those steps. Editing macro will take you to the VBA programming editor. Graphics are available in the Insert Tab. See the screenshots below for various available graphics in MS Excel Click on shape to insert it. Shape will adjust the shape. Choose from the list of available smartArts.
Choose from the list of available Clip Arts. This is where the Vlookup function comes into its own.
Microsoft office excel 2010 formula tutorial pdf free.RINGKASAN MATERI MICROSOFT OFFICE EXCEL
By using our site, you agree to our collection of information through the use of cookies. To learn more, microsoft office excel 2010 formula tutorial pdf free our Privacy Policy.
To browse Academia. Log in with Facebook Log in with Google. Remember me on this computer. Enter the email address you signed up with and we'll email you a reset link. Need an account? Click here to sign up. Download Free PDF. Andi Microsoft office 2010 key product 2018 free. A short summary of this paper.
PDF Pack. People also downloaded these PDFs. People also downloaded these free PDFs. MengenalMicrosoftOfficeolehTaryanaSuryana 1. Yuhefizar-office by Iqbal Adib Syaputra. Office by Karl Baharin. Kuliah Umum IlmuKomputer. Download Download PDF. Translate PDF. Microsoft Excel menyediakan fungsi statistik, fungsi logika serta fasilitas lain yang lebih canggih. Materi yang dibahas pada Modul mputer Definisi komputer adalah alat elektronik yang dapat menerima input data, mengolah data dan memberikan hasil dalam bentuk informasi dengan menggunakan suatu program yang tersimpan di memori komputer dan juga dapat menyimpan program dan hasil pengolahan yang bekerja seca microsoft office excel 2010 formula tutorial pdf free otomatis.
Sistem Definisi sistem adalah jaringan daripada elemen-elemen yang saling berhubungan, membentuk suatu kesatuan untuk melaksanakan suatu tujuan pokok microsoft office excel 2010 formula tutorial pdf free sistem tersebut. Tujuan pokok sistem computer adalah pengolahan data untuk menghasilkan i nformasi. Excel dapat melakukan perhitungan-perhitungan на этой странице kompleks serta mengkonversikan data dengan cepat menjadi grafik dengan berbagai model yang menarik sehingga mudah dibaca dan dimengerti, Microsoft Excel, untuk selanjutnya disingkat Excel, ddalah program aplikasi yang banyak digunakan untuk membantu menghitung, memproyeksikan, menganalisa, dan mempresentasikan data.
Disini kita akan banyak bersinggungan dengan metode-metode pembuatan tabel dan grafik yang sangat dibutuhkan sekali dalam penyusunan data-data perusahaan, hasil-hasil penelitian, maupun dalam pembuatan makalah pribadi. Bekerja dengan Microsoft Excel. Sebelum mulai memasuki pembahasan Microsoft Excel, ada baiknya kita mengenal lebih dulu bagaimana tampilan Microsoft Excel itu, beserta beberapa istilah-istilah umum yang akan digunakan.
Beberapa istilah- istilah umum yang diberikan pada gambar dibawah ini akan banyak digunakan dalam pembahasan selanjutnya, sehingga akan lebih baik bila kita menghafalkannya dengan baik.
Modul ini memberikan kemudahan bagi anda untuk dapat menguasai dasar pokok penggunaan Microsoft Excel yang disajikan secara ringkas dan sistematis, Oleh sebab itu setelah anda tamat modul ini Mdl MsExel 2OO7 M. Pengetahuan Microsoft Excel yang akan anda peroleh tersebut bisa dikembangkan lebih lanjut dengan bantuan Instruktur yang menyajikan modul ini sehingga diharapkan dapat dimanfaatkan tebih luas dan pemakaian yang kompleks.
Title Bar Menampilkan nama program aplikasi dan file yanq aktif Berisika n dereta n yang dapat dipilih dengan mouse dan Menu bar dengan keyboard. Untuk memilih keyboard, gunakan kom binasi tom bol AIt dan huruf yangj bergariS bawah pda menu. Standard Tool Merupakan d. Formattlng Adalah deretan perintah yang terdiri dari icon dimana Tool Bar fu ngsinya ada la h u ntu k memformat lem bara adobe dreamweaver cs5 free full version with serial key free kerja.
Formula tool Tgmpat menampilkan alamat sel aktif, data dan rumus Bar form u la Icon control Merupakan perintah berupa icon yang berfungsi fntdk mengontrol tampilan yaitu Maximize dan minlmize. Status Ba r Adalah baris yalg menampilkan perintah apa yang sedang dilakukan pada lembaran kerja worksheet. Cell Pointer Penun3uK sel aktit column Adalah bagian at? Kepala Kolom Row heading Bagian yang berisi nomor baris.
Gambar 1. Tampilnn Miuosoft Microsoft office excel 2010 formula tutorial pdf free dan beberapa istilah penting. Agar anda dapat menggunakan Microsoft Excel secara optimal, sebaiknya anda memantapkan pengenalan semua elemen dasar di atas terlebih dahulu dari excel Selanjutnya ada beberapa cara untuk memindahkan cell pointer, dan untuk lengkapnya silahkan lihat microsoft office excel 2010 formula tutorial pdf free d ibawa h.
Tabel 1. Yang perlu d ielakka n mengetha u i antar kolom dan baris diingat dan diketahui seluruh elemen yang ada microsoft office excel 2010 formula tutorial pdf free sel Cell. Kolom adalah otomatis diklik dengan dari bebera pa sel yang bagaian yang vertical dan mouse pada sembarang akan mendapat aksi yang d ei beri n a ma beru pa sel pada worksheet sama disebut Range.
Memulai Microsoft Excel: 6. Klik Tools. Klik Options. Klik All Program. Klik View. Klik M icrosoft Office. Klik Gridlines Box. Klik M icrosoft Excel. Klik OK 2. Klik New sebelum enter. KlikBlank Document Klik Office :. Cara lain yaitu dengan mengklik. Klik По этому адресу tanda X pada formula Bar 3.
Menyimpan Naskah 8. Memindahkan Data:. Klik Office Button. Blok drag data yang diinginkan. Klik Save As. Klik Open. Klik fo lder tem pat d ata Cel l ya gn. Ketikan nama file d ima ksud. Klik save. Memanggil Naskah 9. Mengcopy Data: o Button. Klik Nama file yang dimaksud. Klik Open tempat data Cell yang d ima ks ud 5. Keluar dari Microsoft Excel. Klik Close lO. Mengcopy Rumus. Klik Exit atau. Letakkan cursor mouse pada.
Klik ditahan, geser ke tempat. Klik Insert dimana data mau dicopy. Klik Rows. Lepaskan klik l6. Menghapus Baris ll.
На этой странице isi Sel atau isi. Letakkan Pointer di tempat baris Ra nge: mau dihapus. Letakkan pointer atau blok data. Klik Edit yang mau dihapus. Klik Delete. Tekan tombol Delete. Klik OK. Letakkan pointer ditempat kolom mau disisipkan. Menyembunyikan hide Baris:. Klik Insert. Letakkan pointer di baris yang. Klik Columns akan disembunyikan.
Klik Format l2. Menyembunyikan hide Kolom:. Klik Row. Letakkan pointer di kolom yang. Klik Hide akan disembunyikan.
Comments
Post a Comment