LibreOffice Calc – VLookup with dates

Posted on by rcragun

In a different post on this blog, I showed how to use Vlookup to match lists. Someone commented on that post and indicated that it didn’t work with dates. It turns out, it does, but… There is a slight tweak required to make it work. So, if you want to learn how to use VLookup, first go to that previous post. Then come back here to see how this works with dates.

The issue with dates has to do with the format of the cells. To illustrate this, I created a spreadsheet and inserted a bunch of dates:

It’s important to make clear at this point that LibreOffice Calc, just like other spreadsheet software, has different “formats” for cells. Cells can be formatted as numbers, as text, as dates, etc. This is important because LibreOffice will treat cells with different formats differently. For instance, it’s pretty challenging to do numerical calculations with text (e.g., “apple” + 65 = ?). LibreOffice needs to know the “format” of a cell. That is the key to making vlookup work with dates.

To check the format of a cell, all you need to do is right-click on it and select “Format Cells” (or Format -> Cells from the menus at the top of the screen):

Obviously, if you’re working with dates, then the format for the cells should be “Date” and whatever specific date format you want:

Okay, back to vlookup. To find a date in a list of dates, the value you are searching for also has to be a date in the Date format – ideally, they should be formatted identically. You can see in this spreadsheet, I inserted a date to search for in my list of dates:

The vlookup formula isn’t any different. In the cell just to the right of where I inserted the date I want to search for, I added my vlookup formula. Here’s what I put into my formula:

=VLOOKUP(E1,A1:A22,1,0)

This formula calls the “vlookup” function. “E1” is the date for which we are going to search in the list of dates. “A1:A22” is the list of dates in which we are searching. The “1” after that says to use the first item in the list of items we are searching for (kind of a weird requirement). And the “0” tells LibreOffice Calc that the list is not alphabetized or sorted.

Once I put that formula in, LibreOffice Calc will search through the list to see if my date is in the list. I know that my date is there. And, typically, vlookup will return the matching date if it is there, so it should return “8/27/2021”. But, here’s what I get when I hit return on my formula:

Rather than getting the target date, I get a weird number: 44435. Knowing a little bit about LibreOffice Calc, I quickly realized what the problem is: LibreOffice Calc doesn’t know that I’m searching for a date. It found the date, but the field where it is returning the value it found is currently formatted as a “Number,” not a “Date.” See:

To show that it found the matching date, the vlookup cell also needs to be formatted as a Date. So, change the formatting for the vlookup cell:

And once you hit OK, you should now see the date it found:

In short, yes, vlookup works with Dates. You just have to make sure that the cells where the vlookup formula is located are formatted as Dates not as Numbers and you’ll see which Dates match.

 621 total views,  4 views today

LibreOffice – exporting high-resolution TIFF/TIF files

Posted on by rcragun

As a scholar who regularly publishes work with charts and graphs, I’m often confronted with varied requirements from publishers for the format in which they want the charts and graphs. Most often, the format is as a TIFF/TIF file, typically with at least 300 dpi and somewhere around 1500×1500 pixels. I make most of my charts in LibreOffice Calc, though occasionally I make some in R as well.

I have recently been editing several volumes in which I had to manage the charts and graphs of other scholars as well. As the editor, I had to make sure the final images met the criteria detailed above. Since scholars often make charts and graphs in Word, it took a little finagling to come up with a quick and easy way to export the images in the format needed by the publisher. Since I did finally figure this out, I figured I’d post it here so I remember how to do this in the future. Luckily, LibreOffice works extremely well with these formats (for the most part), which makes this quite easy.

LibreOffice Calc

Assuming you have created your chart/graph in LibreOffice Calc, exporting it into a TIF format should be fairly easy, though it requires an unfortunate extra step. Here’s a chart I created in LibreOffice Calc:

The LibreOffice programmers make it so you can just right-click on the graph and select “Export as Image.”

When you do this, you’ll get this pop-up window asking where you want to save the image and, more importantly, the format:

Here’s where you get a problem. If you select TIFF, you’ll get a .tif file, but the resolution will be basically the same as what you see on your screen, like this:

Ideally, LibreOffice would ask you what DPI and resolution you want once you select the TIFF format and would then export the chart in that resolution and you’d be done in one simple step. Alas, that’s not an option when you export from LibreOffice Calc.

What you can do instead is copy your chart, open an empty LibreOffice Writer document, and paste it into the document, like this:

Then, go up to File -> Export, like this:

You’ll get the same prompt as before asking what you want to name the file and format. Name the file and select PNG format then click “Save.” What you’re looking for is the window that pops up next:

In this window, you can change the DPI to 300 (do this first) and then change the width and height (they are typically linked, so, if you change one, the other automatically changes). When you’re done, click “OK.” The file you’ll get will be 300 DPI and whatever pixels you chose:

Now, open that file with any image editing software (I’m using Gwenview on KDE for this example) and simply select File->Save As:

Now select the TIFF format. Once you save it, you’ll have a TIFF file with the proper DPI and resolution per the publisher’s instructions. The resulting TIFF file will be huge, but it will meet the criteria of the publisher:

NOTE:

The other way to do this is to copy your chart into a LibreOffice Draw file that has been modified with a huge area (e.g., 4000×4000 pixels). You can then expand your chart to file the area in the LibreOffice Draw document and then export the image. However, depending on the original format of your chart/graph, you may have to resize the text if you do this, which is a pain. However, this will give you a much larger image file. But the approach above is much easier.

The tutorial above used LibreOffice 6.4.4.2.

 4,704 total views

LibreOffice Calc: Graphs with Two y-axes with Different Scales

Posted on by rcragun

While a bit technical, it’s occasionally useful to plot multiple data series that have very different scales in the same chart. Let me give an example to illustrate. Let’s say I want to see whether the number of Mormon temples being built aligns with the number of Mormon stakes (akin to a Catholic diocese) that are organized over time. (I’m a sociologist who studies religion; you’ll just have to go with my examples.)

However, the number of Mormon temples is in the hundreds while the number of Mormon stakes is in the thousands. If I plot them both on the same chart with the same y-axis (that’s the vertical axis), the number of Mormon temples is going to look really small and I won’t be able to see the variation over time in the number of temples, like this:

The chart shows that stakes have increased, but it looks like the number of temples has barely moved. LibreOffice Calc automatically creates the scale used for the y-axis based on the scale of the larger of the two data series, in this case, the number of stakes. Thus, the maximum value is 4,000 and the minimum is 0. What I want to do in this tutorial is to illustrate how to add a second y-axis on the right side of the chart that uses a different scale that is more appropriate for the number of temples.

To begin with, go ahead and create your chart with at least two data series, as I have shown in other tutorials, like this one. Once you have your chart with two data series complete, now it’s time to add a second y-axis with a different scale.

First, click on your chart then double-click it to open chart editing. Then, select the chart area by clicking on one of the axes (left or right doesn’t matter) and then right-click it. You’ll get a context menu with the option “Insert/Delete axes…” Select that:

In the window that pops up, you’ll see a second column labeled “Secondary Axes.” You want to select “Y axis.”

Click “OK” and you’ll see that a second y-axis has been added to your chart on the right side using the same metric as the left side:

The next steps are pretty straightforward, but before you do them, you should pause and think for a second so you don’t have to go back and undo what you’re about to do. You’re going to change the scale of one of the two y-axes, but which axis do you want to change? There isn’t a right or wrong answer here. Generally speaking, I typically see charts like this with the smaller of the two ranges assigned to the left axis and the larger assigned to the right axis, but, again, it is entirely up to you which way you choose to go. At this point, though, you need to make a decision. Then you can move to the next step.

I’m going to follow my suggestion above and change the y-axis on the left to a scale that fits with the number of temples (so, a smaller range of values) and keep the y-axis on the right with the larger range for the number of stakes. But before I change the scales of the axes, I need to tell LibreOffice which data series is going to align with which axis. Here’s how. Click on one of your data series lines, then right-click it and select “Format Data Series.”

In the window that pops up, you’ll see on the “Options” tab right at the top an option that says, “Align Data Series to” and then “Primary Y axis” (this is the one on the left of the chart) or “Secondary Y axis” (this is the one on the right of the chart). Since I selected the number of temples first, I’m going to leave that one aligned to the Primary Y axis:

Hit OK. Then select the other line (in my case, the number of stakes), right-click it, and select “Format Data Series.” On the “Options” tab, I’m going to select to align this line with the “Secondary Y axis”:

Once you do that, you’ll see that LibreOffice automatically adjusts the scale of the other axis. Here’s how my chart now looks:

You can see that it changed the scale of the Primary Y-axis (the one on the left) to a maximum of 250 to reflect the smaller range of that data series. If you want to customize the scale used, you can always click on the axis you want to modify and then right-click it and select “Format Axis”:

In the window that pops up, you can modify the scale of the axis by clicking on the “Scale” tab. If you want to change the values, click on the box next to “Automatic” to unselect it so you can put in your own values, then customize the value you add, like this:

When you have modified the scale to your satisfaction, select “OK” and your graph will be updated with the scale you want, like this:

The resulting chart now has two axes with different scales. It would be a good idea at this point to label the axes to reflect the differences. Simply right-click on the chart and select “Insert Titles.” In that window, add appropriate titles. The left y-axis is simply the Axes while the right y-axis is considered the “Secondary Axes”:

And your final graph will look something like this:

That’s how you can create a chart with two axes in LibreOffice Calc.

NOTE: This example was done in LibreOffice Calc version: 6.4.2.2 on a Linux-based operating system (Kubuntu 19.10).

 18,739 total views,  4 views today

LibreOffice Calc: Interpolating Missing Values in Graphs

Posted on by rcragun

Here’s my situation. I have some data over time but I’m missing values in specific years. I want to graph that data but would rather not have to estimate all of the missing values. It turns out, LibreOffice Calc can do that for you in your chart. Here’s how…

Imagine I’m plotting the number of congregations in the LDS Church over time (weird example, I’m sure you’re thinking, but I’m a sociologist who studies religion, so, yeah, that’s what I do). I have the number of congregations in 1841, 1849, 1901, etc. Basically, I have the number in certain years, but I’m missing the number of congregations in lots of other years. I could interpolate the missing values (Excel has this function built in; LibreOffice Calc does not, but you can do it following the approach I have detailed here). But, I don’t really need to do that for my project. I just need a chart that shows the growth of congregations over time.

My data are organized into two columns. Column A is years and ranges from 1841 through 2019. Column B is the number of congregations with the values I have and lots of blank cells:

Select the cells you want to plot (A1:B176 in my case) then click on “Insert Chart”:

You’ll get this window:

Since I want a Line chart, I’m going to select “Line” and because I want “points and lines,” I’m going to select that option as well. I also want “Smooth” lines rather than “Straight” lines, so I select that option, too:

Click “Next >” at the bottom. Since you already selected your Data range, you shouldn’t have to change that. However, we do want the “First column as label” for the x-axis of the chart. So, select that option:

Then select “Next >”. You shouldn’t have to change anything on the Data Series tab, so you can hit “Next >” again. On the Chart Elements tab, you’ll want to describe your chart elements. Add a Title and label your x-axis and y-axis. I also didn’t need a legend since I’m only plotting one data series, so I turned that off:

Then click “Finish.” You’ll have a chart, but it only has the points for the years when you have data, like this:

To add a line connecting the points and interpolating the missing data, click on the chart, then double-click it to modify the chart. Once you’re inside the chart, click on one of the points to select the data series, then right-click and select “Format data series”:

On the “Options” tab you’ll see “Plot Options” and just below that, “Plot missing values.” The default is “Leave gap.” Select “Continue line” and it will interpolate the missing values for you:

Select “Ok” and your line chart will now actually have a line, like this:

There you have it. A line chart with interpolated missing values in LibreOffice Calc without you having to calculate all of the missing values.

NOTE: This example was done in LibreOffice Calc version: 6.4.2.2 on a Linux-based operating system (Kubuntu 19.10).

 7,052 total views

LibreOffice Calc – Creating Charts with Conditional Formatting

Posted on by rcragun

I was working on creating a chart in LibreOffice Calc that was kind of weird. Basically, I wanted to show change over time in a dichotomous variable (e.g., political party affiliation in the US – Democrat or Republican). I could, theoretically, make a chart where presence is indicated by “1” and absence is indicated by “0” or “2,” but I didn’t want the chart to suggest that one value was better than the other, which is what such a chart would indicate:

This chart format seems to suggest that Democrats are something and Republicans are not.

I realized, then, that what I needed was a way to tell the software which color to code each bar in the bar chart. Of course, that is possible by clicking on every single bar and customizing the color. But, with my data already indicating whether it should be one color or another and with over 100 cases to code, I was hoping to find a better approach.

I ended up finding a couple of websites (here, here, and here) that discussed a feature in LibreOffice Calc that was introduced in version 4.5 called “property mapping.” That seemed to hold the answer. However, as of LibreOffice 6.0.3 (the version I happened to be using), the “Property Mapping” option in the Data Range window of a chart was gone. Even so, the general process still works. So, here’s how to create the kind of chart I wanted.

First, make sure you’ve got your data entered (obviously). Second, select the data you want to chart:

Select “Insert -> Chart” and then choose “Column” chart:

On the Next page of the Chart Wizard, make sure you’ve got your data selected correctly (I had to select “First column as label”):

The problem with this, of course, is that I have coded everyone in the dataset with a “1” even though, in reality, some of the Governors in this dataset were Republicans and some were Democrats. Right now, it looks like everyone was a Democrat, but this is where the conditional formatting comes on.

In your spreadsheet, you need to set up your conditional coding. This is done with “IF” statements. Here’s the code I used:

=IF(D2="Republican",COLOR(255,0,0),IF(D2="Democrat",COLOR(0,0,255),COLOR(255,255,0)))

Basically, what this code does is checks to see whether a cell has “Republican” in it or “Democrat.” If it is Republican, color 255,0,0 is selected (RED, which is reported in the cell as 16711680). If it is Democrat, color 0,0,255 is selected (BLUE, which is reported in the cell as 255). The last part of the formula indicates that everyone else should have a different color (YELLOW = 255,255,0). Dragging that down, I get a color code for every one of the values in my chart. Once I have this in place (which should really be done before you start the chart wizard, but can be done after the fact), now I can use this to adjust the bar fill color in my chart.

Adding this to the chart is done in the “Data Series” step in the Chart Wizard. Alternatively, if you’ve already created your chart, select it, and right-click. Then click on “Data Ranges” and you can adjust this there. In the Data Series window, under “Data ranges:”, select “Fill Color”:

You’ll note that the “Range for Fill Color” box is empty. We’re going to fill that with the values we just generated using our conditional code. You can do this by clicking on the button next to that empty box, then select the corresponding values:

Once you have selected your fill color, your chart will now have the corresponding conditional fill colors:

Of course, this chart still looks pretty crappy. The y-axis needs to be adjusted, the columns shouldn’t have any space between them, and it needs a title. Here’s my finished chart:

The chart provides a graphical representation of a dichotomous variable using color to illustrate shifts between political parties without suggesting that one party is better than the other. Et voila – conditional color formatting in LibreOffice charts.

 4,105 total views,  4 views today

LibreOffice Calc – Comparing and Aligning Two Lists Using VLOOKUP

Posted on by rcragun

One of the tasks I have to do regularly as part of my job is to compare two lists to see which items are missing on one list but not the other. I have been doing this by hand but figured there had to be a way to do this in Excel. I finally figured it out but don’t want to forget how to do it. So, I’m documenting it here so I can draw on this whenever I need to do it again.

Here’s the scenario. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. During that month, some of the items on my list have been taken off and some have been added to it. Likewise, the same has happened to the other list that this other person sends me (I’m being really vague here because I work at a university and there are laws that govern academic records).

My list and the other list have to be kept synchronized but we have two separate databases that are used for this because… (argh, yeah). Anyway, what that means is that I have to compare the two lists and quickly find the items on one list but not the other and vice versa. Here’s how to do it quickly in LibreOffice Calc.

Here are two lists:

(NOTE: You can actually do this in two separate Calc sheets or within the same one.)

LIST 1 is in Column A and List 2 is in Column C.

In Column B I am going to create a function that allows me to search all of Column C to see which of the items in LIST 1 show up in LIST 2. Click in Row 2 of Column B then click on the Function Wizard (or start typing):

In the Function Wizard dialogue box, you’ll see this:

Search for VLOOKUP then double-click it and you’ll see this:

You now need to build the function. This is where I got confused, so I’m going to try to explain this carefully.

The first box is the “Search criterion.” Basically, this is what you want to find. In our example, let’s say that we want to see which items in List 1 are in List 2 (Hint: It’s apples and grapes.). So, we are going to put in the Search criterion that we want the software to search the items in column A or LIST 1. We do this by simply selecting cell A2 (you can do this by typing it in or by selecting it by clicking the button to the right of the box and then selecting A2 like I did below):

Next is the “Array” box. This is the content through which you want the software to search to find the items in LIST 1. Again, you can type this in or select it using the button to the right. However, there is an important change that you need to make here. If you select the array with your mouse and leave it as is, the values will change as you drag this formula down in Column B as it will assume you want to adjust the array as well. Since we don’t want to adjust the array but rather want to search through the same items in LIST 2, we need to put dollar signs before the letters and numbers in the Array which will lock the boundaries of the array into place so they don’t change when we copy the formula to other cells, as shown below:

The next part is the part that threw me off for a long time in figuring this out. The “Index” is the column in the Array you want to compare to the Search Criterion. In this case, all you need to do is specify “1” since there is only one column. But, presumably, you could have an array made up of multiple columns and want to choose just the 4th column (so you would enter 4 in the Index). Here’s how this looks:

Finally, the last box that is part of our function is the “Sort order” box. This tells the software whether the lists are ordered alphabetically or not. If they are not, enter zero (“0”). If they are, enter “1.” If you leave this blank, LibreOffice Calc thinks the lists are ordered. So, make sure you fill this out.

Once you’ve got that all entered, your formula should look like what I have above. Hit “OK” and it will search the Array C2:C7 for “bananas.” If it finds it, it will return “bananas.” If it doesn’t, it will return “#N/A” as shown below:

You can then, of course, drag this formula down. When it finds the item from LIST 1 in LIST 2 it returns that item. When it doesn’t, you get #N/A.

And there you have it. A LibreOffice Calc function for searching a list for a target and returning an indicator.

(NOTE: What I typically do after I have run this function is sort by the column where the function is located so I know which items are missing from LIST 1 and which are missing from LIST 2.)

 20,428 total views,  2 views today

LibreOffice Calc – splitting contents of cells into multiple columns (e.g., splitting commas)

Posted on by rcragun

I periodically have to take a column of text in LibreOffice calc that has names like this “Lastname, Firstname” and split them into two columns. I figure it out every time, but then I forget how I did. So, here’s a quick tutorial on how this is done.

Open your spreadsheet with the cells that need to be split, like this:

Select the column that you want to split:

Then go up to Data -> Text to Columns:

You’ll get the following window:

This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. I selected just “Tab” and “Comma” but could also select “Space” to get rid of the extra space. However, I’m going to leave the extra space and show you one more function that can be useful in more complex situations. Once you’re done, hit “OK” and you’ll see your single columns split into two:

If you want to get rid of the extra space, there is a LibreOffice Calc function for that. Click in cell C1 and then go up to Insert -> Function. You’ll see this window:

The function you want is in the Text Category (use the dropdown menu) and is called TRIM. Simple TRIM the text in B1 and it will get rid of the extra spaces:

When you’re done with your function, select “OK” and you’ve got your spaces removed. Drag that function down and it will remove all of the spaces:

You can then copy the new column without the spaces and do a “Paste Special” into the old column, overwriting the text with the spaces. Just make sure you turn off the “Formulas” when you do the “Paste Special” and you’ll get just the new text:

Delete the column with formulas and you’re good to go:

 

 48,593 total views,  6 views today