Showing posts with label Spreadsheet Tutorials. Show all posts
Showing posts with label Spreadsheet Tutorials. Show all posts
Web Integration
Monday, 24 June 2013
Web IntegrationA Web Query is when you send a request to a web page and ask for some data to be returned. You'll see how to do that in this section, by importing data into your spreadsheet from a web page on our web site.
Object Linking and Embedding
Monday, 24 June 2013
Object Linking and EmbeddingObject Linking and Embedding (or OLE for short) is a technique used to insert data from one programme into another. We'll create a simple spreadsheet to illustrate the process, and place it in to Word document. When the Excel spreadsheet is updated, you'll see the Word version update itself as well.
Insert Drawing Objects into your Excel Spreadsheets
Monday, 24 June 2013
Insert Drawing Objects into your Excel SpreadsheetsA drawing can liven up a dull spreadsheet. Some good line art, or even simple shapes, can help illustrate your data. In this lesson, you'll see how to add simple shapes, and textboxes to your spreadsheet.
How to Insert Hyperlinks in Excel
Monday, 24 June 2013
How to Insert Hyperlinks in ExcelYou can place Hyperlinks in the cells on your spreadsheet. To quickly go to a different worksheet or workbook, you would simply click the link. We'll see how to do that now.
Click inside of cell A1 of a new spreadsheet
From the Excel Ribbon, click the Insert tab
From the Insert tab, locate the Links panel
Click on Hyperlink:
How to add an error message to an Excel Spreadsheet
Monday, 24 June 2013
How to add an error message to an Excel SpreadsheetIn the previous part, you saw how to add drop down lists to your Excel spreadsheets. In this part, we'll display an error message for our users. If you haven't already done so, you need to do the previous tutorial first.
Dropdown Lists in Excel
Monday, 24 June 2013
Dropdown Lists in Excel
If you have to type the same data into cells all the time, then adding a drop down list to your spreadsheet could be the answer. In Excel, this comes under the heading of Data Validation.
In the example below, we have a class of students on a drop down list. We only have to click a cell in the A column to see this same list of students. You'll see how to do that now. Here's a picture of your finished spreadsheet:
Create a Worksheet Template in excel
Monday, 24 June 2013
Create a Worksheet TemplateCreating a worksheet template can really save you a lot of time. Templates are especially useful if you find yourself having to create the same spreadsheet over and over. For example, if a spreadsheet has sheets for each month of year it becomes a bit if a chore if you have to type out the heading and formula for each sheet. Instead, you only need to do it once. Then you can save it as a template. If you need a new month, you can then Insert your template. Here's how.
Data Forms in Excel
Monday, 24 June 2013
If your spreadsheet is too big to manage, and you constantly have to scroll back and forward just to enter data, then a Data Form could make your life easier. To see what a Data Form is, we'll construct a simple spreadsheet.
But a data form is just a way to quickly enter data into a cell. It is used when the spreadsheet is too big for the screen. To get a clearer idea of what a data form is, try this.
The LOOKUP Function
Monday, 17 June 2013
The LOOKUP FunctionThe LOOKUP function in Excel is used to search one column of data and find data in the corresponding row. For example, if you are searching a column of employee IDs the LOOKUP function can find, say, employee number 12345 in the ID column. Once it has found the ID 12345 it then can return data from that same row. Let's create a spreadsheet to clear things up.
View Docs
Pivot Tables - Part Two
Monday, 17 June 2013
Pivot Tables - Part TwoThe reason why the scores from our Pivot Table are so strange is because Excel is using the wrong formula. It's using a Sum total when we want it to use an Average.
Here's the Pivot Table so far:
View Docs
Pivot Tables in Excel
Monday, 17 June 2013
Pivot Tables in ExcelA Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is being displayed. For example, choose just one student from a drop down list and view only his or her scores. Pivot tables are a lot easier to grasp when you see them in action. Here's the one we're going to create in this section:
View Docs
Custom Names - Constants in Excel
Monday, 17 June 2013
Custom Names - Constants in ExcelYou can set up a custom name to be used as a constant. An example of a constant is PI when working with circles. If you need to use PI in formulas you don't need to type out 3.1415 all the time, you can just do this:
View Docs
Named Ranges in Excel
Monday, 17 June 2013
Named Ranges in ExcelA Named Range is way to describe your formulas. So you don't have to have this in a cell:
= SUM(B2:B4)
You can replace the cell references between the round brackets. You replace them with a descriptive name, all of your own. So you could have this, instead:
= SUM(Monthly_Totals)
Behind the Monthly_Totals, though, Excel is hiding the cell references. We'll see how it works, now.
View Docs
Subscribe to:
Posts (Atom)