Wednesday, February 27th, 2013 at
So far we have just done calculations and a little bit of text. Now we will cover combining text and calculations. The underlying concepts of cell combinations means much more than just numbers can be displayed.
Lets go to C1 and format the cell as text. Remember you do that with a right click and then go down to format. Then within the format selection choose text.
Then go to C2 and enter 5. All cells are set up for values by default so unless you need something special leave that part alone … for now
Go to C3 and enter =C1*A1
At A1 set the value to 3
Now at C1 enter text Apples
At C4 enter =(C3+C1). To do combinations with text the starting and ending () must in place for it to work.
Note the C4 cell has a #VALUE! error. We entered a + where it should have been a &. Go into C4 and enter =(C3&C1). The & function allows you to combine text (in programming lingo you concatenate the values). Putting a + function between cell values and text won’t work. Now the value show up as 15Apples. That’s nice but it can be made to read easier. At C4 enter =(C3&” “&C1) now the value will show as 15 Apples. The “ “ allows you to enter a space into it. This method will allow an infinite amount of adjustment to the final presented value and description. Any text value can be entered in the double quotes.
Yes I know I promised you database info we will get to that but you need to understand a lot of basics first. Going directly to the database calculations now would be an invitation to confusion.
Saturday, February 16th, 2013 at
Each cell can be adjusted for its use and presentation. It can be set for the accuracy of the calculation, money, percentage and more. I’ll show you how to format a cell.
- Left click cell to select it
- Right click mouse
- A floating menu will appear on the right. Use the mouse to scroll down the menu and select format
Cell formatting selections
- Lots of things to play with here
- Number tab adjusts it’s behavior and accuracy.
- Alignment tab adjusts how the cell contents will be displayed.
- Font adjusts both the size, font type and color of the displayed information
- Border adjusts the border around the cell. Size, color and line type selections.
- Fill adjusts the color of the cell.
- Protection tab does two things. It doesn’t have much particle use. Unless you are sending it to someone that you can’t trust to use it right. Then perhaps it has a use.
i. Hides the value/calculation in the cell
ii. Locks the cell from changing
I invite you to explore this area thoroughly. There is enough going on in this little area that a good explanation of all the options could take a month of posts. Many of these items should be self explanatory. I’ll take questions on individual selections.
Tuesday, February 12th, 2013 at
Opening Excell the first time can be intimidating. The functions across the top of the screen and all those little empty boxes.
1) Take note of the Letters across the top and the numbers on the rows. If look at the very top left cell it’s location is A for the column and 1 for the row or A1. Calculations can use the information at that location. The calculations can be textual or numerical. Most of the time the calculations will be the first use but there are other usages for the text that are unexpected
i. Goto cell B1
ii. Enter =
iii. With the mouse select A1
iv. Enter a +
v. Put in a value of 5 and enter
vi. The total in cell B1 will be 5
vii. Choose A1 enter a 10
viii. Note that the value in B1 changes to a 15
ix. Select B1 and you will see the following display
x. That is the basic usage of Excel referencing other cells to create a new value
xi. The B1 in the upper left side shows the cell selected
xii. To its’ right the =5+A1 shows the calculation in that cell
Very very basic now try it yourself
- Choose A2 and enter 24
- Choose B2 and enter 12
- Choose C2 and enter = A2/B2
- See that the answer is .5
- Edit C2 and change the calculation to = A2*B2 ( The * indicates a multiplication)
- Now choose all three cells A2&B2&C2 and pull downward
- It will populate A3&B3&C3
- Choose C3 and note that the calculation is the same but it now references A3 * B3
That’s enough to start with. There are other secrets to copying and references coming soon
Keep up the practice there’s lots to learn
Sunday, February 10th, 2013 at
This is not to sing the praises of Microsoft it’s to show what can be done with a readily available tool with some customizations.
Excel by itself has lots of places and functions to explore and I’ll take you though a few. There are too many capabilities in Excel to learn them all. I’m going to take you on a trip into new ways to use it.
Warning this will go well into the programming zone but I’ll take you one small step at a time.
You will learn:
- How to program cells for individual calculations
- How to do automated searches for data
- Dynamic extraction of data
- How to turn Excel into a moving text marque
- How to rearange data by using Visual Basic
- How to create a interface to control your program
- How to speed up your programs execution
So hold on we are just getting started. Real exercises to come soon. After you learn a few tricks it will get much much easier.
English: Illustration of subroutine in Microsoft Excel that reads the x-column, squares it, and writes the squares into the y-column. All proprietary Microsoft art work has been cropped to leave a generic spreadsheet (Photo credit: Wikipedia)